VBA Format() and decimal places - not performing as I'd expected

Thank you for looking at my question,

I am extracting number values from text strings such as in the example below:

strstring = "Storage Zone                  :                                           Maximum Order Quantity        :  99999999.0000 [pcs],"

strString = Right(String, len(String)-74)

 lngMaxOrdQty = CLng(getTextWithUnits(strStringRight))

Function getTextWithUnits(s As String) As String
    Dim Arr1() As String
    Dim Arr2() As String
   
    Arr1 = Split(s, ":", , vbTextCompare)

    If InStr(Arr1(1), "  ") > 0 Then
        Do Until InStr(Arr1(1), "  ") = 0
            Arr1(1) = Replace(Arr1(1), "  ", " ")
        Loop
    End If
   
    Arr2 = Split(Arr1(1), " ", , vbTextCompare)
   
    getTextWithUnits = LTrim(Arr2(1))
End Function

The function extracts the value "99999999.0000" as a string but when I convert it to a long value it loses the decimal point and the four decimal places - 99999999

I have tried Format(lngMaxOrdQty, "0.0000") but this still returns 99999999

Please can anybody tell me what I've missed
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

As a long it only shows significant numbers the decimal point and the zeros are not significant

Format transforms your long  in a string again
Sub Macro()
strString = "Storage Zone                  :                                           Maximum Order Quantity        :  99999999.0000 [pcs],"
strStringRight = Right(strString, Len(strString) - 74)

  lngMaxOrdQty = CLng(getTextWithUnits(strStringRight))
MsgBox Format(lngMaxOrdQty, "0.0000")
End Sub

 Function getTextWithUnits(ByVal s As String) As String
     Dim Arr1() As String
     Dim Arr2() As String
     
     Arr1 = Split(s, ":", , vbTextCompare)

     If InStr(Arr1(1), "  ") > 0 Then
         Do Until InStr(Arr1(1), "  ") = 0
             Arr1(1) = Replace(Arr1(1), "  ", " ")
         Loop
     End If
     
     Arr2 = Split(Arr1(1), " ", , vbTextCompare)
     
     getTextWithUnits = LTrim(Arr2(1))
 End Function

Open in new window


Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary CroxfordOperations Support AnalystAuthor Commented:
Thank you for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.