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

 

[pgc01]

Hi Fin Fang Foom

An UDF solution. Use as an excel formula

=ExtractAlpha(A2)

Paste in a module:

Function ExtractAlpha(rC As Range) As String

With CreateObject("VBSCRIPT.REGEXP")
    .Pattern = "[^a-z]"
    .Global = True
    .IgnoreCase = True
    ExtractAlpha = .Replace(rC.Value, "")
End With
End Function

Hope this helps
PGC

 

[Fin Fang Foom]

Hi pgc01,


Thank you for repyling. Yes your function does work. But I should've said if we can have a formula solution not a vba solution or any add-ins. Because if I email my co-workers with your add-in they will get a pop up message about a virus and must set your macro secruity. Is it possible to have a formula solution?

 

[pgc01]

Hi again

I may be wrong but I think it's not possible.

The question with problems like yours, that happen frequently, is that excel does not provide any text function to concatenate an array or a range of cells (the equivalent of SUM() for numbers).

In fact you cannot write

=Concatenate(A1:A3) which you would like to be A1 & A2 & A3

This is a basic functionality that precludes a lot of otherwise simple text formulas.

That's why MCONCAT from the MOREFUNC add-ins is so popular.

So, I may be wrong but I think you really need vba or an add-in.

Best regards
PGC

 

[Fin Fang Foom]

Thanks pgc01 I really do appreciate taking your time on this thread. Then I will probably use a vba solution.


Thank You!

 

[Yogi Anand]

But I should've said if we can have a formula solution not a vba solution or any add-ins.

Hi Fin Fang Foom:

Here is a formula based solution -- however it does use MCONCAT function from MoreFunc Add-in ...

Book1
	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	ghlfdcwg
8	f1ZZ	fZZ
9	12348548op	op
10	urrttq89	urrttq
11
Sheet9

array formula in cell B2 is ...

=MCONCAT(IF(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>64,MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

this is then copied down.

 

[Fin Fang Foom]

Thank for the formula Yogi Anand.

 

[Krishnakumar]

Still using MOREFUNC..

=REGEX.SUBSTITUTE(A2,"[^a-zA-Z]")

Or..

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,""),"\",""),";",""),"/","")

Book1
	A	B	C	D
2	1f2d4g1g56g	fdggg	fdggg
3	z1g5g	zgg	zgg
4	\g1h5gf	ghgf	ghgf
5	214g21g5g	ggg	ggg
6	2n5g	ng	ng
7	g7h69l;f9d2cw1g	ghlfdcwg	ghlfdcwg
8	f1ZZ	fZZ	fZZ
9	12348548op	op	op
10	urrttq89	urrttq	urrttq
Sheet2

HTH

 

[Fin Fang Foom]

Thank you Krishnakumar it works well.

 

[Derek]

Hi there

If you wanted a quick one-off solution just to remove numbers from column A, you can use this formula in B1.

=SUBSTITUTE(A1,COLUMN(A1)-1,"")

Scroll the formula right to column K, then down. The answer is in colum K

regards
Derek