Macro to Delete only Alpha Strings but not Alphanumeric or Numeric Strings in a Cell

**papageorgio** · 01-19-2016, 06:32 AM

Hi There,

I require a macro that will delete all the strings in a cell that consist only of Alpha characters.

E.g.
I have a list of data in column A as per below

1 x 123ABC Washing Machine
2 x 234BCD Dish Washer
3 x 345CDE Refrigerator

I would like to remove that data that only consists of all text, so that the above strings would look like below.

1 123ABC
2 234BCD
3 345CDE

Any assistance would be much appreciated.

**davesexcel** · 01-19-2016, 06:36 AM

Would it be safe to say, you want to keep the string between the 2 spaces?

**jindon** · 01-19-2016, 06:46 AM

Assuming the data in col.A

Sub test()
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\b(\d+|[a-zA-Z]+)\b"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            r.Value = Application.Trim(.Replace(r.Value, ""))
        Next
    End With
End Sub

**zbor** · 01-19-2016, 06:55 AM

How about this function:

Function NUMEXTRACT(rng As Range, Optional delim As String)
Dim Arr() As String
Dim x As String

If Len(delim) = 0 Then
    delim = " "
End If

Arr() = Split(rng, delim)

For i = LBound(Arr) To UBound(Arr)

For j = 1 To Len(Arr(i))
If IsNumeric(Mid(Arr(i), j, 1)) Then
    x = x & delim & Arr(i)
    Exit For
End If
Next j
Next i

NUMEXTRACT = Trim(Mid(x, 2, Len(x)))

End Function

use it as any other function:

Formula:



=NUMEXTRACT(A1, " ")

Where you have range and delimiter (in your case space).
if no delimiter space will be default so

Formula:

=NUMEXTRACT(A1)

will work too.

Macro to Delete only Alpha Strings but not Alphanumeric or Numeric Strings in a Cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to Delete only Alpha Strings but not Alphanumeric or Numeric Strings in a Cell

Re: Macro to Delete only Alpha Strings but not Alphanumeric or Numeric Strings in a Cell

Re: Macro to Delete only Alpha Strings but not Alphanumeric or Numeric Strings in a Cell

Re: Macro to Delete only Alpha Strings but not Alphanumeric or Numeric Strings in a Cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Remove only numeric strings from a value in the cell

Extract Unique and Sort Array of Alpha (strings) and Numeric (longs)

Extracting required strings from alpha numeric value in a row in excel

Macro Sort Alphanumeric Strings Row by Row

Macro to delete alpha and alpha numeric values

summing numeric parts of alphanumeric strings

Extract Numeric and Alpha strings from Alphanumeric String

Bookmarks

Bookmarks

Posting Permissions