extract text out of a string [SOLVED]

	A	B
1
2	125 BUSINESS CENTER DR	125
3	127 BUSINESS CENTER DR	127
4	132 BUSINESS CENTER DR	132
5	143 BUSINESS CENTER DR	143

for the above table, I used the below code to extract numerical values in strings in column A and put the result in column B,
"=LOOKUP(99^99,--(0&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)+1)))))"

Could you help me edit this code so that now the remainning texts will result in column C
(i.e. C2 will be "BUSINESS CENTER DR")

Thank you

If your data looks always like this you can use left and right functions

Hi and welcome to the forum

Try this:

=TRIM(MID(A2;MAX(ISNUMBER(MID(A2;COLUMN(1:1);1)*1)*COLUMN(1:1))+1;2^15))

Array formula CSE. Not just Enter.

Hope to helps you.

it doesn't always look like this. Many different street names

So please attach a dummy file with your possible different street names with expected results.

This will work based on your sample

B2, copy down...

=LEFT(A2,FIND(" ",A2)-1)+0

C2, copy down...

=TRIM(SUBSTITUTE(A2,C2,""))

cse>Array formula,means, that when you copy the formula, dont click just enter, but Control+Shift+Enter >>ALL together!