Hi!

I'm sure someone has a nice little UDF that does this. In the
meantime.....how about some real fun with a couple of hacks?

If you want the final result to be in column F you need an additional helper
column. I'll use column G for the example:

Enter this formula in G1:

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

Enter this formula in F1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(G1,6,""),7,""),8,""),9,""),0,"")

Now, for a real "classic" hack that doesn't need a helper column but is
specific to where the original data is and where you want the extracted
text:

Create this named formula:
Name: subst
Refers to:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(INDIRECT("rc[-5]",FALSE),6,""),7,""),8,""),9,""),0,"")

Then, enter this formula in F1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(subst,1,""),2,""),3,""),4,""),5,"")

In the named formula the reference: INDIRECT("rc[-5]",FALSE), refers to the
cell in the same row as the formula and 5 columns to the left. So, if the
formula is entered in cell F1, INDIRECT("rc[-5]",FALSE), refers to cell A1.
Same row, 5 columns to the left.

Biff

"famdamly" wrote in
message ...

First of all thanks to everyone for the assistance.

Perhaps it would be easier to show an example of sample data and the
desired result.

Sample data Desired result


A1 label3 F1 label
A2 item5 F2 item
A3 label2 F3 label
A4 6r F4 r
A5 3p F5 p


--
famdamly
------------------------------------------------------------------------
famdamly's Profile:
http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=516690