Extract until first letter

[sshinshaw]

I have a string of numbers and letters and need to extract everything after the first letter.

Example

456trust
4file
5867hampton
24grate

 

[DanteAmor]

Do you only need the letters, right?

 

[sshinshaw]

Correct.

 

[tyija1995]

So you want to see trust instead of 456trust, file instead of 4file (etc etc)?

Try this UDF:

Function NoNumbers(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d"
        NoNumbers = Trim(.Replace(txt, ""))
    End With
End Function

Then =NoNumbers(A1)
Will give you "trust" if A1 is the cell containing "456trust" for example.

EDIT: Nested a trim to tidy.

 

[sadboy309]

Click (Ctrl+Shift+Enter) to the end of line formula:

{=RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0}, A1, ROW(INDIRECT("1:" & LEN(A1)))), 0)))}

 

[XOR LX]

Hi,

Array formula**:

=MID(A1,MATCH(1,-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))+1,99)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[sshinshaw]

Thanks!!!

 

[DanteAmor]

I did not like it, but it's a regular formula

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