Extract letters from cell

[Fin Fang Foom]

Hi everyone,



I would like a formula to extract all letters in a cell but in that cell it also has numbers. I would like to extract the letters only.


Example:

HYPHEN2.xls
	A	B	C	D
1			Extract letters
2	1f2d4g1g56g		fdggg
3	z1g5g		zgg
4	\g1h5gf		ghgf
5	214g21g5g		ggg
6	2n5g		ng
7	g7h69l;f9d2cw1g		ghdcwg
8	f1ZZ		fZZ
9	12348548op		op
10	urrttq89		urrtq
11
12
Sheet2

 

[Boller]

Define String

Refers to box:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet2!$A18,1,""),2,""),3,""),4,""),5,""),6,""),7,"")

In C2,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(String,8,""),9,""),0,""),"\",""),";",""),"/","")

Expanding on this :-

Instead of the long formula in C2, this could also be assigned to a Name and referred to by the other Named formula.

To make it easier to set up, here's a macro to create the two names.
It only needs to be run once and then it can be deleted.

Sub Add_Names()
ActiveWorkbook.Names.Add Name:="Nbr2", RefersToR1C1:= _
"=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1],0,""""),1,""""),""\"",""""),"";"",""""),"":"",""""),""/"","""")"
ActiveWorkbook.Names.Add Name:="Nbr", RefersToR1C1:= _
    "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Nbr2,2,""""),3,""""),4,""""),5,""""),6,""""),7,""""),8,""""),9,"""")"
End Sub

In the cell immediately to the right of the cell with the alpha-numeric data enter =Nbr