Take the 2-minute tour ×

I want to extract numbers from a string. The strings are written in each cell like this.

1, 1
1, 2
1, 3

Numbers are simply divided by commas.

How do I extract numbers from them in Excel VBA?

Thank you very much.

share|improve this question
You can use Split(value, ",") - this will return a variant array –  Tim Williams Nov 11 '11 at 21:53
What is the desired string outcome of the above? 11 12 13? –  Issun Nov 12 '11 at 0:58
add comment

3 Answers

up vote 4 down vote accepted

If I got your question right, you have "1, 1" in cell A1, "1, 2" in cell A2, "1, 3" in cell A3.

If you want respectively the numbers before comma in cells B1, B2 and B3 and the numbers after the comma in cells C1, C2 and C3 you can do the following:


Public Sub test()
    'the variables' declaration has been corrected, check 
    'the post's comments below to find out which correction were made
    Dim lngLeft as Long, lngRight as Long, lngCommaPos As Long
    Dim intI As Integer
    For intI = 1 To 3
        lngCommaPos = InStr(1, Range("A" & intI).Value, ",")
        lngLeft = Left(Range("A" & intI).Value, lngCommaPos - 1)
        lngRight = Right(Range("A" & intI).Value, Len(Range("A" & intI).Value) - lngCommaPos - 1)
        Range("B" & intI).Value = lngLeft
        Range("C" & intI).Value = lngRight
    Next intI
End Sub

NON VBA solution:

Insert the following formula in cell B1:
=VALUE(LEFT(A1,FIND(",", A1)-1))

Insert the following formula in cell C1:
=VALUE(RIGHT(A1,LEN(A1)-FIND(",", A1)-1))

Copy cells B1 and C1 Paste into cells B2 to C3

If you want to have strings in columns B and C (instead of having numbers) you can drop the VALUE function, and the formulas in cells B1 and C1 will be

share|improve this answer
Think that =LEFT(A1;FIND(",";A1)-1) should be =LEFT(A1,FIND(",",A1)-1) and so on –  barrowc Nov 12 '11 at 0:20
I have an italian installation of Excel, and the function arguments are separated by semicolons. I thought that it worked the same way in any other language. My bad. Well at least I just learnt something new about Excel :) –  Bruno 82 Nov 12 '11 at 0:36
A little unrelated, but you do realize that lngLeft and lngRight are being declared here as a variable, right? VBA does not allow you to declare variables like you are doing in the code. :) –  Issun Nov 12 '11 at 1:13
I couldn't really grasp what you said, then I made a search on the internet and found out that if I declare my variables this way: Dim lngLeft, lngRight, lngCommaPos As Long lngLeft and lngRight will be dimensioned as Variant. Did you mean that I'm supposed to declare the variables Dim lngLeft as Long, lngRight as Long, lngCommaPos As Long? @Issun –  Bruno 82 Nov 12 '11 at 16:02
You got it perfect. In VB you can omit all the "As Long"s but in VBA, you have to put each one in there, otherwise only the last variable is declared as the type you wanted. :) Not that your code doesn't work at all, but I figured you didn't know that since you were using the prefix "lng" on them. :) –  Issun Nov 13 '11 at 0:40
show 3 more comments

*Assumption is that the desired result is 11, 12, and 13.

The below have been written as functions, but can easily be changed to sub routines instead. I didn't want to get into setting ranges and just concentrate on the function.

If your data is just numbers seperated by commas and spaces, then just use replace:

Function ExtractNumber(ByVal text As String) As String

ExtractNumber = Replace(text, ", ", "")

End Function

If you would like a more sophisticated function that will extract all numbers regardless of whatever may else be in the string, here's my RegexExtract function. By default, I set it up so that it will comma-seperate all captures, but you can specify it as none:

=RegexExtract(A1, "(\d)", "")
  • (\d) means to capture any numbers 0-9
  • 3rd parameter is how you'd like all captures to be seperated (if at all)

Here is the function:

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional seperator As String = ", ") As String

Application.ScreenUpdating = False
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String

RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.Count - 1
    For j = 0 To allMatches.Item(j).submatches.Count - 1
        result = result & (seperator & allMatches.Item(i).submatches.Item(j))

If Len(result) <> 0 Then
    result = Right$(result, Len(result) - Len(seperator))
End If

RegexExtract = result
Application.ScreenUpdating = True

End Function
share|improve this answer
add comment

I f you don't want to use VBA you could also use Text to Columns, see: http://support.microsoft.com/kb/214261

share|improve this answer
add comment

Your Answer


