Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging text and data fields without loosing zeroes ie .00l
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging text and data fields without loosing zeroes ie .00l
replace the # with 0
"Bricap" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging text and data fields without loosing zeroes ie .00l
=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 On Sun, 30 Dec 2007 15:56:00 -0800, Bricap wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merging text and data fields without loosing zeroes ie .00l
=A1&" "&DOLLAR(B1,2)&" "&C1
"Bricap" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging fields question | Excel Worksheet Functions | |||
Lookup based on two fields without merging? | Excel Worksheet Functions | |||
Merging fields | Excel Discussion (Misc queries) | |||
Merging Worksheets - Fields Of Same Data In Different Location | Excel Discussion (Misc queries) | |||
Fix too few data fields error message when merging excel and word | Excel Discussion (Misc queries) |