How to convert VBA decimal DataTypes

255 pts.
I need to figure out how (if possible) to remove decimals from numerical data, and add zeroes to the front/back end of data so that I can concatenate several cells into a single strain. Ex: Actual value = 14.37, needs to be converted to 14370 Actual Value = 4.3, needs to be converted to 04300 Actual Value = 0.3, needs to be converted to 00300 These values would then be concatenated into 143700430000300 Any help?!?! I am new to VBA, but have tried several different methods w/o the needed result.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Judging from your examples, I assume you always want to start two places to the left of the decimal, and include up to three places to the right of it. That precludes any number 100 or greater. Here’s the code:

Sub concatnumbers()
n1 = 14.37
n2 = 4.3
n3 = 0.3
longstring = fivechar(n1) + fivechar(n2) + fivechar(n3)
MsgBox longstring
‘to test with other numbers
n4 = InputBox(“Enter number to convert”)
MsgBox fivechar(n4)
End Sub

Function fivechar(n) As String
If n >= 100 Then
fivechar = “ERROR”
bign = (1000 * n) + 100000
fivechar = Mid(CStr(bign), 2, 5)
End If
End Function

<kccrosser> The above uses some cute tricks, but in general there is a simpler answer – just use the basic VBA string functions. The following can be shrunk, but it illustrates the distinct steps. Note – this “assumes” the input is always in a numeric range of 0.000 to 99.999. To be safe, I would normally check the range, as in the previous answer.

Function format23(ByVal rIn As Double) As String
Dim i As Integer
Dim sOut As String
Dim sIn As String
Dim sPre As String
Dim sPost As String

sIn = rIn ‘ convert number to string
sPre = “00”
sPost = “000”
sOut = “”
i = 1
While (i <= len(sIn)) and (Mid(sIn, i, 1) <> “.” ) ‘ look for a decimal point (might not be there)
sOut = sOut & Mid(sIn, i, 1) ‘ add to output if not a decimal point
i = i + 1
sPre = Left(sPre, Len(sPre) – 1) ‘ trim the leading zeroes when not needed
sOut = sPre & sOut & Mid(sIn, i + 1, 3) & “000” ‘ prepend with 0-2 zeros, copied number, trailing fractional part, and 3 “0” characters
sOut = Left(sOut, 5) ‘ take the leftmost 5 characters
format23 = sOut ‘ done
Exit Function

End Function

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • McNitro
    I'm dealing with a few more formats than just 5 char. Some are 7, or 8, or 4...but I see where your answers are leading me. I was able to obtain the result I needed in Excel using the TEXT function (multiplying data by 10, 1000, 10000 when needed to remove the decimal). Ex.: If ("A1") is 4.73...and need a 5 character value... I multiplied by 1,000 and formatted A1 as "00000". This give me 04730. Then in cell B1 I was able to use the TEXT function to alleviate any issues with "zeroes" falling off my value (ex.: =TEXT(A1, "00000"). Once I was able to do this with all the necessary cells, I was able to concatenate the data into one string. I was just wondering if this method may cause me problems in the future. What will the VBA functions mentioned provide me with that this Excel method may not?
    255 pointsBadges:
  • SbElectric
    I firmly believe in KISS principle – so I will stick with your Excel cell formatting options. Only potential problem may be if any data is negative (denoted either by color or by negative sign in front or at end of data) or if the data has 3 or more decimal places. SbElectric
    2,540 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: