CODEWITHSUNDEEP
excelexcel-2007vba
js0823
js0823

Reputation: 1873

Excel VBA : How do I extract number from a string?

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.

Upvotes: 3

Views: 13951

Answers (3)

LeasMaps
LeasMaps

Reputation: 302

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

Upvotes: 1

Bruno 82
Bruno 82

Reputation: 519

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:

VBA SOLUTION:

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
=LEFT(A1,FIND(",",A1)-1)
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)

Upvotes: 6

Gaijinhunter
Gaijinhunter

Reputation: 14685

*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))
    Next
Next

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

RegexExtract = result
Application.ScreenUpdating = True

End Function

Upvotes: 4

Related Questions