Pulling numbers from a character string

[ttocs]

Hello everyone. I'm new to the forums and this is my first post. I've been looking for a VBA community to help me learn and I think I may have found one. So, first and foremost, Hello!

Ok, now to my question :)

I'm trying to write something that will pull some characters out of a cell and put them in the column to the left of it. This can either be in excel format or using VBA, whatever works better. I'll give an example of what I'm looking for below:

I have a SQL query that will run against a database and return results like this:

usa, ca (920)
usa, il (619)
usa, ak (997)
usa, mo (640)

I need the column before this one to have the (920) section. This way, the users can search using an AutoFilter on the numbers. It doesn't matter in the cell the numbers are coming from have the numbers or lose the numbers because of this, but the usa, ca part must stay. So, in conclusion, I'm looking for this:

I want this: (sorry for horrible formatting...)

A................B..................C
<null> | usa, ca (920) |
<null> |usa, il (619) |
<null> |usa, ak (997) |
<null> |usa, mo (640) |

To become this:

A..............B.................C
920 | usa, ca (920) |
619 | usa, il (619) |
997 | usa, ak (997) |
640 | usa, mo (640) |

Thank you in advance!

[Bob Phillips]

You can do it with formulae, but if you want VBA



Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim mPos1 As Long
Dim mPos2 As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To LastRow

mPos1 = InStr(.Cells(i, "B").Value, "(")
mPos2 = InStr(.Cells(i, "B").Value & ")", ")")
If mPos1 > 0 Then

.Cells(i, "A").Value = Mid(.Cells(i, "B").Value, mPos1 + 1, mPos2 - mPos1 - 1)
End If
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

[ttocs]

Thanks so much! This worked perfectly!

[david000]

Bob!

I love this code, but can this line do without the .Value & ")" ?

mPos2 = InStr(.Cells(i, "B").Value & ")", ")")
Like this?
mPos2 = InStr(.Cells(i, "B").Value, ")")

[Bob Phillips]

It could as long as the data is good, but I was catering for the possibility that there was no closing bracket. By adding a bracket, I ensured that I would get the position of the last bracket. IF it is already there, adding another does no harm.