×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Keeping zero as a real value in the Concatenate Function

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
   

Tony Abbey  www.fetraining.com

RE: Keeping zero as a real value in the Concatenate Function

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.

RE: Keeping zero as a real value in the Concatenate Function

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.

RE: Keeping zero as a real value in the Concatenate Function

(OP)
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
 

RE: Keeping zero as a real value in the Concatenate Function

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.
 

RE: Keeping zero as a real value in the Concatenate Function

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.

RE: Keeping zero as a real value in the Concatenate Function

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.

RE: Keeping zero as a real value in the Concatenate Function

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.

RE: Keeping zero as a real value in the Concatenate Function

(OP)
electricpete,

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

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.

RE: Keeping zero as a real value in the Concatenate Function

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.

 

RE: Keeping zero as a real value in the Concatenate Function

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?

RE: Keeping zero as a real value in the Concatenate Function

In it's simplest form...
=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

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.

RE: Keeping zero as a real value in the Concatenate Function

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.

RE: Keeping zero as a real value in the Concatenate Function

(OP)
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  www.fetraining.com

RE: Keeping zero as a real value in the Concatenate Function

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.

RE: Keeping zero as a real value in the Concatenate Function

Use SUBSTITUTE:

=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

Good idea!

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources