Keeping zero as a real value in the Concatenate Function 1

[fetraining]

I use the Concatenate function a lot to group data into a comma deliminated string to paste into various FEA input files.

A simple example is to calculate x,y,z grid coordinates, say (23.4,45.6,78,9) stored in A2:A4 and then produce a string such as:
" GRID,23,23.4,45.6,78,9"
where GRID is a keyword and 23 is the integer ID stored in A1.

I use:

Concatenate("GRID",A1,",",A2,",",A3,",",A4)

The problem is if I have a 0.0 coordinate, then the Concatenate function returns 0 (integer) in the string. Most FE codes are very picky and need a real value as 0.0, and will fail on 0.

I would prefer to avoid using a macro or VB as it is often done 'on the fly'.

Any suggestions very welcome,

regards,
Tony


Tony Abbey

http://www.fetraining.com

 

[electricpete]

create a named range: myformatstring
with the following contents *;*;0.000
(note no quotes is required... just don't start it with equal or plus so it is interpretted as string vs formula)

Then use the formula:

=+CONCATENATE("GRID",TEXT(A1,myformatstring),",",TEXT(A2,myformatstring),",",TEXT(A3,myformatstring),",",TEXT(A4,myformatstring))

Note that putting the format string into separate location instead of typing the formula allows you to easily tweak the format string to get exactly what you want.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

You can also incorporate some spacing and comma's into the format string which is passed as an argument to text. Perhaps this makes it a little cleaner than typing them into the formula.

define myformatstring as " "*;*;0.000000
define myformatwithcomma as " ,"*;*;0.000000

Use the formula:
=+CONCATENATE("GRID",TEXT(A1,myformatstring),TEXT(A2,myformatwithcomma),TEXT(A3,myformatwithcomma),TEXT(A4,myformatwithcomma))


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[fetraining]

electricpete,

thanks for the tips on TEXT and formating via a named range. That has given a very neat solution.

I experimented with the the data to go in the range. I am not clear what the implication of *;*;0.000 is. Now you have given me the hint I have read up on named ranges and format controls and see the usage of # and 0 and . etc - but I am being a bit thick here and don't follow what * does. I am setting up the range as one cell. I can see 0.000 won't work alone. I think I can see that *0.000 or '0.000 or similar tells Excel to preserve contents as text in this cell. Not sure about *;*;0.000.

thanks for your patience,

regards,

Tony

 

[IRstuff]

EP's string is a FORMAT command, nothing to do with being in a cell, per se.

TTFN

FAQ731-376

 

[jghrist]

You may want to consider saving the file as a .txt file. By default, this will produce a tab delimited file. You can change to a comma delimited through Windows.

Change the separator in a CSV text file:

Click the Windows Start menu.
Click Control Panel.
Open the Regional and Language Options dialog box.
Click the Regional Options Tab.
Click Customize.
Type a new separator in the List separator box.
Click OK twice.

Note After you change the list separator character for your machine, all applications will use the new character. You can change the character back to the original character by using the same procedure.

 

[electricpete]

Here is some info on custom number formats:

http://www.ozgrid.com/Excel/CustomFormats.htm

Exactly how the *;*;0.000 works, I'm not sure. I used trial and error to get the string that worked the way I wanted.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Actually, when I wrote that string, I was under the impression that * specified the general format. My intention was to leave + and - alone and format 0's. Apparently somehow the + and - pick up the format of the 0's. Perhaps that what the * does... pick up the next valid format to the right.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Here is a file set up to easily show the effects of several different format strings, all intended to give a zero displayed with a decimal.


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

By the way, the positive negative input numbers on the left to be formatted are a multiple of pi... they have many more significant figures than displayed.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

A slightly better presented version of the previous file is attached

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[fetraining]

electricpete,

thanks - the sample spreadsheet you uploaded is very useful for experimenting with,

regards,

Tony


Tony Abbey

http://www.fetraining.com

 

[electricpete]

You're welcome. One more revision to add a few more items. It looks like the last format I used is good to always output 10 figures (Regardless of where the decimal lies), except for 0.

General;"-"General; 0.0#







=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[Denial]

ElectricPete's final example works fine, but would be better if the quotation signs were removed from the minus sign. Thus
General;-General; 0.0#

As a general (no pun intended) warning concerning the use of custom formats, the second section (the one for negative numbers) should always include an explicit means of showing their negativeness. For engineers, this usually means including an explicit minus sign.

Otherwise negative numbers will appear positive.

Quite a few years ago I was using a spreadsheet developed by someone else. In essence, this spreadsheet calculated the buoyancy of a floating object. Its answer was displayed as a single number, that being the object's freeboard. Nearly all problems solved by the spreadsheet did float, and the issue was how high they floated. However when prettying up his spreadsheet for distribution the author had applied a custom format to the cell containing the freeboard, and he had done so without including an explicit minus sign for negative numbers. When the spreadsheet was applied to a problem where the object would actually sink, the calculated freeboard was negative but it displayed and printed as positive. Potentially catastophic.

 

[melone]

Couldn't you just format the cells with the coordinates as text? This would ensure that what you see is what you get in the concantenation, right?

 

[brengine]

In it's simplest form...
=Concatenate("GRID",TEXT(A1,"0.0"),",",TEXT(A2,"0.0"),",",TEXT(A3,"0.0"),",",TEXT(A4,"0.0"))

Ken

 

[electricpete]

I don't think that would work. It would give you one decimal point for everything (so 0.0356 comes out as 0.0!)

If economy of keystrokes were your goal, you might use something like 0.0###### as long as you are sure there are enough #'s to cover your needs. If you're not sure how small the numbers will be, I would stick with General;-General; 0.0



=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Ken - sorry for my last post. Your solution might work fine depending on the accuracy required. I notice the original post only listed one decimal point.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[fetraining]

ElectricPete

my original intention was to just avoid integer 0's. But you guys have opened my eyes to all sorts of options - thanks!

Just to make it more interesting, the actual format is an 8 column field, a real number must have a decimal in it. This means in practice that it is a compromise on number of decimal places, dependent on size of number. E format is allowed and in fact can be abbreviated to a + or - to save sapce i.e. 1.2345+6 (there is a wide field format when higher accuracy is really important).

regards,
Tony


Tony Abbey

http://www.fetraining.com

 

[electricpete]

0.00E+00 will give you scientific notation - 8 characters including the decimal point, E and +.

0.000E+0 gives you one more digit precision to work with if you're sure you'll never go above E+9 or E-9.

I'm not sure how to get rid of the E

=====================================
Eng-tips forums: The best place on the web for engineering discussions.