merging text and data fields without loosing zeroes ie .00l

[Bricap]

How do I merge text and data fields without loosing zeroes to the right of
the decimal point? .00

I'm using the following 2 formulas to merge more that one field into a new
merged field containing all the text and #'s from the individual fields.

=A1&" "& TEXT (B1,"$ #,##.##")&" "&C1 (this combines 3 fields)

or =Q13&""&H14&""&M14&""&G14&""&N14&""&O14&""&P14 (this compines 7 fields)

The problem I'm having is: One of the fields that I am grabbing is a number
field to 2 decimal place i.e. 12.50, but when I merge the fields together,
the number is truncated to 12.5 I do not want to loose my zeroes. even if
one of my original fields is 5.00.

 

[LanceB]

replace the # with 0

 

[Gord Dibben]

=A1&" "& TEXT (B1,"$ #,##0.00")&" "&C1 will return 2 DP for B1

For the second formula you would have to prefix each 2 DP cell with the TEXT
function and provide the format as above.

=Q13&" "&TEXT(H14,"$ #,##0.00") &" "&M14&" "&G14&" "&TEXT(N14,"$ #,##0,00") etc.

If you would like a macro that combines the cells you choose and leaves the
format as is.

Format the numeric cells to 2 DP then run this macro.

Sub ConCat_Cells()
Dim X As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set X = Application.InputBox("Select Cells, Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In X
If Len(y.text) > 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - Len(w))
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub


Gord Dibben MS Excel MVP

 

[Teethless mama]

=A1&" "&DOLLAR(B1,2)&" "&C1