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

1. ## 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. ## 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. ## 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. ## 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?

5. ## 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>

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

Always whole numbers

7. ## 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. ## 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. ## 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>

#### Posting Permissions

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