Need help for Text-To-Columns Split at first Number

[marshg]

Hi:
I have a long list of business names and addresses that are in one column. I'm hoping to used text to column to split them using a wildcard # so it can split/separate into new cell at the FIRST # it encounters.
Example:
City Books Inc 1111 E Carson St Pittsburgh
Eljay's Used Books 1309 Newtown Rd Pottstown

I can't use space to be the text-to-column delimiter because the businesses have different length names, but anything that begins with a number indicates beginning of address. Of course this still leaves me later with the issue of separating/splitting off the city into it's own cell, but that's not as important [though if you have a solution to that, I'm very eager to know]. So is there a way to have Excel do Text to column split at the first # it encounters?
any help is appreciated,
thanks,
Marshall

 

[Jonmo1]

Welcome to the board...

This ARRAY formula should get you started.
It will return the position # of the first # in the string.
You should then be able to use it in a Left/Right/Mid type of formula to split the string at that point..

=MATCH(TRUE,ISNUMBER(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)+0),0)
With the string in A1

IMPORTANT:
This is an array formula that requires CTRL + SHIFT + ENTER
After entering the formula, highlight the formula cell and press F2
Then press CTRL + SHIFT + ENTER
When entered correclty, the formula will be enclosed in {brackets}

Hope that helps..

 

[marshg]

jonmo:
this is more sophisticated than I'm used to doing, but I will give it a try

 

[marshg]

jonmo:
thanks so much for the wicked fast reply! Alas, this is more sophisticated than I'm used to doing. I tried to follow directions, but I've never worked with array before and couldn't seem to do anything helpful [when I did the formula as I understood your instructions] the whole column turned to 0.

but your help/effort is much appreciated!

marshall

 

[Domenic]

Here's another way... Assuming that A2 contains the text string, try...

B2:

=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1))

C2:

=TRIM(REPLACE(A2,1,LEN(B2),""))

 

[Jonmo1]

jonmo:
thanks so much for the wicked fast reply! Alas, this is more sophisticated than I'm used to doing. I tried to follow directions, but I've never worked with array before and couldn't seem to do anything helpful [when I did the formula as I understood your instructions] the whole column turned to 0.

but your help/effort is much appreciated!

marshall

After you put in the formula...
Highlight the cell holding the formula
Press F2
Then press CTRL + SHIFT + ENTER (Meaning all 3 keys at same time)
When it's entered correctly, the formula will be enclosed in {brackets}
Then you can fill the formula down the column


Anyway, go with Domenic's formulas...They get you all the way to solution, mine just gives the position #...

 

[marshg]

Guys:
I so appreciate your help, but alas, I can't seem to make use of it. For instance do I put the whole formula:
B2:

=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1))

C2:

=TRIM(REPLACE(A2,1,LEN(B2),""))

at the top of the column [in an empty cell] and press CTRL + SHIFT + ENTER and it will work?
sorry to be thick/inexperienced here. I can tell you're giving me good advice!
marshall

 

[Domenic]

Let's assume that Column A, starting in Row 2, contains the data. Enter the first formula in cell B2, confirm with just ENTER, and then copy/paste or drag the formula down the column. Then, enter the second formula in C2, confirm with just ENTER, and copy/paste or drag the formula down the column.

 

[marshg]

Brilliant!! I wish I could buy you a beer (or whatever your favorite drink is)!
thanks so much. The truly great thing would be if I could understand how to do it myself.
really really appreciated.
wishing you the best!
Marshall