remove English letter in cells
 

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?

remove English letter in 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
---------------
Jacob Skaria


"Wu" wrote:

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?

remove English letter in cells
 

Please have a look in the below link

http://office.microsoft.com/en-us/ex...549011033.aspx

--
If this post helps, pls click Yes
---------------
TGV


"Wu" wrote:

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?

remove English letter in cells
 

On Tue, 11 Aug 2009 19:16:02 -0700, Wu wrote:

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?

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