Seperating text/numbers

[sobrien]

I have the following text in A1, A2 ETC..............
1RICHMOND
13COLLINGWOOD

In cell B1, B2 etc...I want the text only.

Thanks in advance

 

[Ron Coderre]

Try this:

=MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX
($A:$A,1):INDEX($A:$A,LEN(A1))),1)))+1,LEN(A1))

Does that help?

 

[jbeaucaire]

I've been using the custom function found here for that task:

Extract Text from Alphanumeric String

...the UDF is in post #2, works great.

 

[jbeaucaire]

Couldn't get Ron's to work, so a little Googling turned up this formula which did work for me:

=SUBSTITUTE(A1,LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(A$1:INDEX(A:A,LEN($A1))))),"")

 

[sobrien]

thanks that formula works but is three rows below where I want it?? - i.e instead of showing up in B1 it is in B3??

 

[sobrien]

All workinng now thanks guys!!

 

[Ron Coderre]

I don't know why the formula I posted would not work

B1: =MID(A1,SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX
($A:$A,1):INDEX($A:$A,LEN(A1))),1)))+1,LEN(A1))

When copied down...
These are the values and returned values:

13COLLINGWOOD     COLLINGWOOD
1richmond         richmond
15Main            Main
10235Grand        Grand

Am I missing something?

 

[jbeaucaire]

Sorry Ron, yes, it works. My system was including a hidden character when I copy/pasted that befuddled me temporarily.