remove English letter in cells

  • Thread starter Thread starter Wu
  • Start date Start date
W

Wu

I have the data in different cells contain number and English letter,

For example:

1209N
WS123
B10-0909C
0908M


Result:

1209
123
10-0909
0908

How can I just remove the English letter in these cells?
 
Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

Col A Col B
1209N =CROPALPHA(A1)
WS123 =
B10-0909C =
0908M =

Function CropAlpha(strData As String)
For intTemp = 1 To Len(strData)
If UCase(Mid(strData, intTemp, 1)) Like "[A-Z]" = False Then _
CropAlpha = CropAlpha & Mid(strData, intTemp, 1)
Next
End Function

If this post helps click Yes
 
I have the data in different cells contain number and English letter,

For example:

1209N
WS123
B10-0909C
0908M


Result:

1209
123
10-0909
0908

How can I just remove the English letter in these cells?
Click to expand...

You can do this with a User Defined Function (UDF).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=RemAlpha(A1)

in some cell.

=============================
Option Explicit
Function RemAlpha(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.ignorecase = True
re.Pattern = "[A-Z]"
RemAlpha = re.Replace(s, "")
End Function
=================================
--ron
 
Back
Top