Keeping zero as a real value in the Concatenate Function
Keeping zero as a real value in the Concatenate Function
(OP)
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
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 www.fetraining.com





RE: Keeping zero as a real value in the Concatenate Function
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.
RE: Keeping zero as a real value in the Concatenate Function
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.
RE: Keeping zero as a real value in the Concatenate Function
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
RE: Keeping zero as a real value in the Concatenate Function
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Keeping zero as a real value in the Concatenate Function
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.
RE: Keeping zero as a real value in the Concatenate Function
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.
RE: Keeping zero as a real value in the Concatenate Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Keeping zero as a real value in the Concatenate Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Keeping zero as a real value in the Concatenate Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Keeping zero as a real value in the Concatenate Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Keeping zero as a real value in the Concatenate Function
thanks - the sample spreadsheet you uploaded is very useful for experimenting with,
regards,
Tony
Tony Abbey www.fetraining.com
RE: Keeping zero as a real value in the Concatenate Function
General;"-"General; 0.0#
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Keeping zero as a real value in the Concatenate Function
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.
RE: Keeping zero as a real value in the Concatenate Function
RE: Keeping zero as a real value in the Concatenate Function
=Concatenate("GRID",TEXT(A1,"0.0"),",",TEXT(A2,"0.0"),",",TEXT(A3,"0.0"),",",TEXT(A4,"0.0"))
Ken
RE: Keeping zero as a real value in the Concatenate Function
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.
RE: Keeping zero as a real value in the Concatenate Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Keeping zero as a real value in the Concatenate Function
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 www.fetraining.com
RE: Keeping zero as a real value in the Concatenate Function
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.
RE: Keeping zero as a real value in the Concatenate Function
=SUBSTITUTE(CONCATENATE("GRID,",TEXT(A1,"0.0000E+0"),",",TEXT(A2,"0.0000E+0"),",",TEXT(A3,"0.0000E+0"),",",TEXT(A4,"0.0000E+0")),"E","")
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Keeping zero as a real value in the Concatenate Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.