Extract only text

[Kcprakash]

Dear friends,
pl solve this for me

1TN2013=TN
12PON2013=PON
123ASSM2013=ASSM

 

[bobhc]

Good day Kcprakash

You seem to want all the work doing for you with out you having to do any thing, this question as been asked again and again one of the most asked question one of the most answerd with various ways to achive what you want.
If you put a small amount of effort in to looking and searching the forum you will find what you need.

 

[Luke M]

Very cumbersome, but using the "big sledgehammer" approach, you could do this if it's a XL 2007+ file:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8",""),"9",""),"0","")

 

[SirJB7]

@Luke M
Hi!
What a pity that this array formula doesn't work! Why is Excel so weird sometimes?
=SUBSTITUTE(A1,ROW(1:10)-1,"")
Regards!

 

[Luke M]

@SirJB7
I'll admit, that was my first try as well.

 

[json]

Very cumbersome, but using the "big sledgehammer" approach, you could do this if it's a XL 2007+ file:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8",""),"9",""),"0","")

HAHAHAHHAHAHAHA

 

[oldchippy]

@json,

At least it works, BTW you missed an "A" out

HAHAHAHAHAHAHAHA

 

[json]

@oldchippy,
I'm not knocking Luke's solution... just found the formula 'funny'

I must have coughed in the middle of my laughing

 

[jacques]

Without using VBA or UDF has anyone found a better way than using nested substitutes? It does the job, but I believe there must be a better way.

I have also tried the following, but this only returns the first character:
{=CONCATENATE(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),"",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))}

 

[SirJB7]

@jacques
Hi!
I'm afraid that no one achieved the simple and reasonable way. Or unless he didn't say anything.
Regards!