Extract letters from a string of characters.

Marvinexcel

New Member
Joined
Oct 14, 2016
Messages
16
Good Day,

Please advise how to extract letters only from a string of characters with formula.

in A1 - 123.,/ ';m-a=r_12vi123n;

result in B1 should be marvin

Thanks in advance.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about a UDF (user defined function)...
Code:
Function Letters(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[A-Za-z]" Then Letters = Letters & Mid(S, X, 1)
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Letters just like it was a built-in Excel function. For example,

=Letters(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
Here is another UDF that doesn't require looping through all the characters individually. If your actual cell text could be considerably longer than your one example, and you have this formula in many cells, the avoidance of looping could make this udf faster. If the cell texts are quite short, then it may be that Rick's udf is slightly faster. If speed becomes an issue for you, you will need to do some testing to decide which is best in your particular circumstances.

Code:
Function Ltrs(S As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "[^a-zA-Z]"
  Ltrs = RX.Replace(S, "")
End Function

Excel Workbook
AB
1123.,/ ';m-a=r_12vi123n;marvin
Sheet1
 
Last edited:
Upvote 0
Please advise how to extract letters only from a string of characters with formula.
If this meant a standard worksheet formula & you have Excel 2016 through Office 365, then try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
AB
1123.,/ ';m-a=r_12vi123n;marvin
2123.,/ ';m-A=r_12vi123N;mArviN
Sheet2
 
Upvote 0
Thank you Sir Peter for the reply, I am not a 2016 user so I'll go with your UDF. Many thanks for detailed response. :):):)
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top