Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Keeping zero as a real value in the Concatenate Function 1

Status
Not open for further replies.
fetraining

fetraining

Aerospace
Jul 29, 2008
17
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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
EP's string is a FORMAT command, nothing to do with being in a cell, per se.

TTFN

FAQ731-376
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Here is some info on custom number formats:

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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
 http://files.engineering.com/getfile.aspx?folder=288681ca-cedd-4d35-8005-dd8e5d7e5a75&file=FormatExamples.xls
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
 http://files.engineering.com/getfile.aspx?folder=d6cff005-50f3-41be-b15a-94028d3f3df7&file=FormatExamplesR2.xls
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
In it's simplest form...
=Concatenate("GRID",TEXT(A1,"0.0"),",",TEXT(A2,"0.0"),",",TEXT(A3,"0.0"),",",TEXT(A4,"0.0"))

Ken
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Denial
  • Locked
  • Question
Early alternative to ISFORMULA() function 1
2
Replies
20
Views
2K
3DDave
3DDave
n8cole
  • Locked
  • Question
Looking for an AI that will help me create a chemical functional group database
Replies
7
Views
3K
pierreick
pierreick
Histor
  • Locked
  • Question
Autogenerate Typical Rows based on input value
Replies
4
Views
281
SkipVought
SkipVought
Kenja824
  • Locked
  • Question
Index Match not knowing the range? 2
Replies
8
Views
873
SkipVought
SkipVought
vinodnx7
  • Locked
  • Question
count the same text from the file and create new list with number
Replies
19
Views
1K
vinodnx7
vinodnx7

Part and Inventory Search

Sponsor

Back
Top Bottom