Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove a letter from a number? | Excel Discussion (Misc queries) | |||
Remove any letter from a referenced cell | Excel Discussion (Misc queries) | |||
Remove any letter from a referenced cell | Excel Discussion (Misc queries) | |||
Formula to remove a letter from one cell to another | Excel Discussion (Misc queries) | |||
remove first letter | Excel Worksheet Functions |