Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting words and not numbers in a cell (2003)

    Hello Everyone,

    Is there a way to delete words or text in a cell, but not numbers? For example cell a1 has in it TEst_HOnda_2223 and cell a2 might have Test_Honda_Accord_2224 and so forth. I need to delete everything in the cell excel the number at the end. Any help would be great.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting words and not numbers in a cell (2003)

    Option 1:

    In cell B1, enter this array formula (confirm with Ctrl+Shift+Enter, not just Enter):

    =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$50) ,1)),0),255)

    and fill down as far as needed (source: Array Formulas)

    Option 2:

    Run the following macro:

    Sub ExtractNumbers()
    Dim r As Long
    Dim n As Long
    Dim i As Integer
    Dim strVal As String
    Dim c As Integer
    n = ActiveSheet.Range("A65536").End(xlUp).Row
    For r = 1 To n
    strVal = Range("A" & r)
    For i = Len(strVal) To 1 Step -1
    c = Asc(Mid(strVal, i, 1))
    If c < 48 Or c > 57 Then
    Exit For
    End If
    Next i
    If i < Len(strVal) Then
    Range("A" & r) = Val(Mid(strVal, i + 1))
    Else
    Range("A" & r).ClearContents
    End If
    Next r
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting words and not numbers in a cell (2003)

    Is there any way to extract numbers in a text field based upon giving a macro a value to look for? FOr instance 1500GAL : giving a dialog box GAL and having it find all the numbers in a list of descriptions that contain "GAL" next to the number? Then LITERS can be supplied to the macro so it pulls out that group of numbers from the description fiels?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting words and not numbers in a cell (2003)

    Will they always be whole numbers or might there be decimal points to worry about?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting words and not numbers in a cell (2003)

    Assuming whole numbers, you could try this:
    <pre>Sub TestFind()
    Dim RegExp As Object, match
    Dim strPattern As String, strText As String
    Dim rngCell As Range
    strText = InputBox("Enter text to search for", "Search text")
    If Len(strText) = 0 Then Exit Sub
    Set RegExp = CreateObject("vbscript.regexp")
    strPattern = "bd+" & strText
    With RegExp
    .Global = True
    .Pattern = strPattern
    For Each rngCell In Selection
    Set match = .Execute(rngCell.Value)
    If match.Count Then
    rngCell.Offset(0, 1) = Val(Left(match(0).Value, _
    Len(match(0).Value) - Len(strText)))
    End If
    Next rngCell
    End With

    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting words and not numbers in a cell (2003)

    Always whole numbers

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting words and not numbers in a cell (2003)

    I copied the Macro into a Module in the workbook. It seemed to do nothing. It asked for the text to search for, I entered GAL and I think I read that it should put the number it finds in a new cell to the right 1 column. It did nothing with the description field 525 GAL PAINT, for instance.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting words and not numbers in a cell (2003)

    Try this:

    Sub ExtractNumbers()
    Dim oCell As Range
    Dim strVal As String
    Dim c As Integer
    Dim strText As String
    strText = InputBox("Enter text to search for")
    If Len(strText) = 0 Then Exit Sub
    For Each oCell In Selection
    c = InStr(1, oCell, strText, vbTextCompare)
    If c > 0 Then
    oCell.Offset(0, 1) = Val(Left(oCell, c - 1))
    End If
    Next oCell
    Set oCell = Nothing
    End Sub

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting words and not numbers in a cell (2003)

    Sorry, I didn't take spaces between the numbers and the search text into account as there were none in your example. Try this revision:
    <pre>Sub TestFind()
    Dim RegExp As Object, match
    Dim strPattern As String, strText As String
    Dim rngCell As Range
    strText = InputBox("Enter text to search for", "Search text")
    If Len(strText) = 0 Then Exit Sub
    Set RegExp = CreateObject("vbscript.regexp")
    strPattern = "bd+s?" & strText
    With RegExp
    .Global = True
    .Pattern = strPattern
    For Each rngCell In Selection
    Set match = .Execute(rngCell.Value)
    If match.Count Then
    rngCell.Offset(0, 1) = Val(Trim(Left(match(0).Value, _
    Len(match(0).Value) - Len(strText))))
    End If
    Next rngCell
    End With

    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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