CONCATENATE or TEXT function to retain specific # formating

T

TDC21

Board Regular
Joined
Mar 31, 2017
Messages
97
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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.
 
Upvote 0
kelly mort said:
You always have i simpler way to do things. I am learning alot here
Click to expand...
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.
 
Upvote 0
Oh okay. Thanks for the info. I am now enjoying the power of excel. One day i will also be solving problem for others!
 
Upvote 0
kelly mort said:
Oh okay. Thanks for the info. I am now enjoying the power of excel. One day i will also be solving problem for others!
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Retain cell format in text reference
Replies
3
Views
59
shaowu459
shaowu459
R
  • Solved
Combing text and formatting date
Replies
4
Views
57
Fluff
Fluff
R
  • Question
Conditional Concatenation
Replies
2
Views
348
RBusiness
R
A
  • Question
Formatting for linked textbox on worksheet
Replies
0
Views
273
artz
A
B
  • Question
TEXT function and Data Table issue
Replies
2
Views
113
bigleauge123
B

Forum statistics

Threads
1,214,659
Messages
6,120,783
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top