CONCATENATE or TEXT function to retain specific # formating

[TDC21]

Hello all,

I am trying to combine 2 cells to create a generic ID but I am having trouble finding the correct formula to retain the appropriate formatting.

Cell D2 = 0.2500
Cell E2 = SA516-70

The desired result is 0.2500_SA516-70

I have tried =TEXT(D3,"#.####")&"_"&E3 as well as =CONCATENATE(D3,"_",E3) but neither retains the number formatting for cell D2

Any suggestions would be greatly appreciated

 

[Weazel]

try something like...

=TEXT(D2,"0.0000")&"_"&E2

 

[VBA learner ITG]

Hello,

Have you considered having D2 cell converted into a text format cell which will retain the 0.2500 as inputted.

If D2 is converted to a Number formatted cell you will loose the 00 at the end.

=CONCATENATE(D2,"_",E2)

I am working on Microsoft 365 for Mac and this works with the above solution.

 

[TDC21]

Thanks Weazel, that worked perfectly

 

[Weazel]

you're welcome

 

[Rick Rothstein]

try something like...

=TEXT(D2,"0.0000")&"_"&E2

You can make your formula three characters shorter by eliminating one of the concatenations...

=TEXT(D2,"0.0000\_")&E2

 

[kelly mort]

You can make your formula three characters shorter by eliminating one of the concatenations...

=TEXT(D2,"0.0000\_")&E2

You always have i simpler way to do things. I am learning alot here

 

[Rick Rothstein]

You always have i simpler way to do things. I am learning alot here

By way of explanation... the second argument to the TEXT function is a text constant where some characters have special meaning to the function. Basically, the TEXT function substitutes parts of values from the first argument for those characters having special meaning, any other text is left as is and remains untouched. If you want a character that has special meaning to the TEXT function to not have that special meaning, you can place a backslash in front of that character to take its special meaning, the TEXT function will then treat it as just a plain character and display it as is.

 

[kelly mort]

Oh okay. Thanks for the info. I am now enjoying the power of excel. One day i will also be solving problem for others!

 

[63falcondude]

Oh okay. Thanks for the info. I am now enjoying the power of excel. One day i will also be solving problem for others!

There is no time like the present! In my experience, helping others has been a great way to learn new tricks and build on prior knowledge/skills.