Extract letters only

[Jborg]

Hi Guys!! Is there a way to extract only the letters with a formula?? 35cht654ghy7656fdse

<colgroup><col style="width: 48pt;" width="64">
<tbody>

</tbody>

 

[jacqueslegrand]

It would work with a macro function :

Function extract_letters(ByVal CurrString As String)


Dim i As Integer


extract_letters = ""


For i = 1 To Len(CurrString)


If Not (IsNumeric(Mid(CurrString, i, 1))) Then extract_letters = extract_letters & Mid(CurrString, i, 1)


Next i


End Function

 

[Jborg]

Thanks Jacque.....you have been very helpful and fast, but i am not good in VBA. Is there a way with a fromula??

 

[Scott Huish]

With a UDF:

Function ExtractLetters(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Za-z]"
    .Global = True
    ExtractLetters = .Replace(r, "")
End With
End Function

Excel Workbook
	A	B
1	35cht65	cht
2	4ghy765	ghy
3	6fdse	fdse
Sheet1

If a native formula is possible at all, then we need to know if your sample is truly representative of your data. Is text always preceded by numbers, with or without numbers after it? Is that the only place the text will appear? etc.

 

[jacqueslegrand]

Thanks Jacque.....you have been very helpful and fast, but i am not good in VBA. Is there a way with a fromula??

Without VBA, I think it would be rather difficult.

But with vba, it is not complicated, you just have to do the following :
ALT + F11 (opens the VBA editor)
then in the menu, Insert > module
then just copy paste in the module you just created the code I gave you

an then, back in Excel, use the function extract_letters just like any other function

 

[Jborg]

Hi Scott

Sorry to have mixed you up.

Real text should read:
00120John0024
00121Steve0025
00122Karl0026

and so on.

Thanks for your great help

 

[jacqueslegrand]

Then try =MID(A1;6;LEN(A1)-9)

 

[Gary's Student]

Here is a non-VBA formula, brute force:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0","")

 

[Jborg]

Thanks Jacques....You have been great. I managed at last.

This is great!!!!

 

[Jborg]

Thanks Gary,

That was great too!!!