## How to convert VBA decimal DataTypes

255 pts.
Tags:
Concatenate
VBA
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.

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”
Else
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.

<pre>
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
Wend
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
</pre>