# 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

[vb]format(val(replace("987-65-43","-",vbNullString))*100+1,"###,###,###")[/vb]

• 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!