Extract letters from a string of characters.

[Marvinexcel]

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.

 

[Rick Rothstein]

How about a UDF (user defined function)...

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]

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.

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
	A	B
1	123.,/ ';m-a=r_12vi123n;	marvin
Sheet1

 

[Peter_SSs]

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
	A	B
1	123.,/ ';m-a=r_12vi123n;	marvin
2	123.,/ ';m-A=r_12vi123N;	mArviN
Sheet2

 

[Marvinexcel]

Thanks so much for quick reply.

 

[Marvinexcel]

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.

 

[Marvinexcel]

Thank you Sir Rick, It is working fine. thanks so much.

 

[Peter_SSs]

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.

Glad to contribute an option.