Converting a text string into a number string

    Hello all,


    Here is the situation, I am working on a macro that extras folder names that are located in a directory. I've been able to isolate a unique text string that looks like this "987-65-43". Now I need to be able to convert that text into a single number with two additional digits. So macro would work to convert the text string "987-65-43" to the number 987,654,301. So in a sense it would remove the dashes and the number would then be multiplied by 100 to move the new number string over two spaces and then add 1 to the number in order to get 987,654,301. Please help me figure out this process.


    Please note that the text string will always be in the format "###-##-##" and the new number would be put into a variable called proindex.


    Thank you for any assistance you can provide.

    Re: Converting a text string into a number string


    Hello Krishnakumar


    I tried adding your code to my project and I get an overflow error.


    Here is how I incorporated it:

    Code
    projnum = Left(jobarray(3), 9)
projindex = Format(Val(Replace(projnum, "-", vbNullString))*100+1, "###,###,###")


    Don't know why it is giving the overflow error.

    Re: Converting a text string into a number string


    What is the projnum definded as? Is it


    Code
    Dim ProjNum as String

    Re: Converting a text string into a number string


    yeah I have it dimmed as String.


    I did that because the projnum is in the format "###-##-##" when I do the Left function.

    Re: Converting a text string into a number string


    It might be projindx - do you have that as long? This works for me


    Code
    Sub Convert()
Dim Cell2Conv As String, ConvertedCell As Long
Cell2Conv = ThisWorkbook.Sheets("Sheet1").Cells(1, 1)
ConvertedCell = Format(Val(Replace(Cell2Conv, "-", vbNullString)) * 100 + 1, "###,###,###")
End Sub

    Re: Converting a text string into a number string


    Thanks M40wen, I changed my projindex as Long and now it works. Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login