• 411K Members
• 5,957 Online
• 466K Conversations

## How to combine cells without loosing various decimal places? (i.e., 0.50+U=0.50U, or 10.0+U=10.0U)

Occasional Visitor

# How to combine cells without loosing various decimal places? (i.e., 0.50+U=0.50U, or 10.0+U=10.0U)

How do I combine or concatenate columns of data where one column is numbers with various decimal places and the other column is text?  I do not want to loose the decimals places.

Concatenate would truncate the decimals places in the examples below to: 0.1U, 10U, & 1U.  But I want the result to look like column 3.

Such as these columns:

1              2             3

0.10    +     U   =    0.10 U

10.0    +     U   =   10.0 U

1.000  +     U   =   1.000U

Thank You.

2 Replies

# Re: How to combine cells without loosing various decimal places? (i.e., 0.50+U=0.50U, or 10.0+U=10.0

Hi,

use the TEXT() function to set a number format.

Highlighted

# Re: How to combine cells without loosing various decimal places? (i.e., 0.50+U=0.50U, or 10.0+U=10.0

Hi,

You can use this formula for this first row:

`=TEXT(A1,"0.00")&B1`

This one for the second row:

`=TEXT(A2,"00.0")&B2`

And this one for the third row:

`=TEXT(A3,"0.000")&B3`

Since you have multiple formats in the first column, you have to change the format in the TEXT function each time.

This is really painful!

To my knowledge, there is no way in native Excel to extract the format of the cell and pass it to the TEXT function, so you have to change it manually each time.

But always there is a solution in VBA.

I've written the custom function below in VBA, which can do this for you:

`Public Function ConcatKeepFormat(Value1 As Range, Value2 As Range) As String   Dim cellFormat As String   cellFormat = Value1.NumberFormat   ConcatKeepFormat = Format(Value1, cellFormat) & Value2End Function`

Please find the attached workbook to test it.

To locate the code, press Alt+F11, and find it in the Module1 of the current workbook project.

Hope that helps

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies