Text to columns up to a number

[aisko]

I am trying to separate a column that contains a company name followed by the street address in the same cell.

Is there a way to separate these into two columns? The street address is not always the same number of characters from the left or right of the cell. Below is an example of how they appear now. I would like to separate the 123 Main St into a separate column via formula to copy down for the entire column.

Example:

Company Name 123 Main St


Thank you for any assistance you may be able to provide.

 

[Scott Huish]

Can it be assumed that you company names won't contain numbers?

If so, here you go:

Excel Workbook
	A	B	C
1	Company Name 123 Main St	Company Name	123 Main St
Sheet1

 

[aisko]

yes

 

[Scott Huish]

I edited my post. My answer is there.

 

[aisko]

Amazing, thank you.

It works for most of them. I am getting a #VALUE! error on several.

Could it be a formatting issue?

In one example, the same company has a different address in two different rows. The one containing "Company, Inc. 123 W Monroe" worked while the one containing "Company, Inc. 1234 Edgewood Road NE" did not work.

There are several others that did not work for any of the various addresses. Would there be an issue with the formula if the address portion of the first cell contains numbers in several locations, for example 745 7th Avenue.

I am hoping it is just that some cells are formatted differently than others.

Do you have any thoughts?

 

[Scott Huish]

I'm not having problems with those, there are 2 formulas you need to copy down though.

Excel Workbook
	A	B	C
1	Company, Inc. 1234 Edgewood Road NE	Company, Inc.	1234 Edgewood Road NE
2	Company, Inc. 745 7th Avenue	Company, Inc.	745 7th Avenue
Sheet1

 

[aisko]

I copied the formula down. I have about 1000 rows of data. It worked for roughly 40% of those rows.

Is there any reason why the rest would not work? Formatting of the original cell?

I can't seem to figure out why the replace formula is returning an error on some and not others.

 

[Scott Huish]

That formula won't even error on an empty cell. Did you change anything in the formula?

 

[aisko]

Ah, you are correct. I left off the second 0. I should have just copied and pasted your formulas.

Thank you so much for your help.