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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,329
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows
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:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows
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
 

Marvinexcel

New Member
Joined
Oct 14, 2016
Messages
16

ADVERTISEMENT

Thanks so much for quick reply.
 

Marvinexcel

New Member
Joined
Oct 14, 2016
Messages
16
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. :):):)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,333
Messages
5,528,089
Members
409,801
Latest member
Jamaira

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top