Consulting

Results 1 to 5 of 5

Thread: Pulling numbers from a character string

  1. #1
    VBAX Newbie
    Joined
    Oct 2008
    Posts
    3
    Location

    Pulling numbers from a character string

    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!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    You can do it with formulae, but if you want VBA

    [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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    Oct 2008
    Posts
    3
    Location
    Thanks so much! This worked perfectly!

  4. #4
    VBAX Tutor david000's Avatar
    Joined
    Mar 2007
    Location
    Chicago
    Posts
    275
    Location
    Bob!

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

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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •