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.
3 Answers
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)
8 Comments
barrowc
Think that
=LEFT(A1;FIND(",";A1)-1) should be =LEFT(A1,FIND(",",A1)-1) and so on
Bruno 82
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 :)
Gaijinhunter
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. :)
Bruno 82
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
Gaijinhunter
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. :)
|
*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
Comments
I f you don't want to use VBA you could also use Text to Columns, see: http://support.microsoft.com/kb/214261
Split(value, ",")- this will return a variant array