How to determine the string?

Question

I would like to keep char A-Z and a-z only, remove all numbers and space.

Such as

"30 ABC30" should be "ABC"

Does anyone have any suggestions?

Thanks in advance for any suggestions

Answer 1

Insert a module in the Visual Basic Editor, and copy the following code into the module:

Function StripString(s As String) As String

    Dim t As String

    Dim i As Long

    Dim c As String

    For i = 1 To Len(s)

        c = Mid(s, i, 1)

        If UCase(c) >= "A" And UCase(c) <= "Z" Then

            t = t & c

        End If

    Next i

    StripString = t

End Function

You can use this function in VBA, for example

Debug.Print StripString("30 ABC30")

will return ABC in the Immediate window.

You can also use the function in a worksheet formula. For example, if cell A1 contains the string "30 ABC30", the formula =StripString(A1) in another cell (for example B1) will return "ABC" (without the quotes).

Answer 2

Thanks, to everyone very much for suggestions :>

Answer 3

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

Edit - Didn't notice that your Excel version is 2003. 2003 will allow only 7 levels of nesting. Hence, you will have to split formula in 2 parts and put in 2 different cells.

B1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,""),2,""),3,""),4,""),5,""),6,"")

C1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,7,""),8,""),9,""),0,"")," ","")

If you want one shot solution then, please use solution provided by HansV MVP.