Script/Macro for detailed Text-To-Columns related issue?

I have a large spreadsheet about 13,000 lines.

Column A is the ShipToName information but I received the data with the name and address in the same field.

So all the names and addresses are combined in column A.

I need to get the addresses in column B.

Now the ONLY real pattern I can find is that the majority of addresses start with a number like "Bob Joe and Co. - 123 east 123 west Las Vegas, Nevada"

Is there a way to run a script that moves everything into column B after it hits a number? Start checking from the front of the string and when it hits a number move all thats remaining to the column on the right?

I have tried for hours doing text to columns and sorting trying to find patterns but it is not efficient whatsoever. Or maybe it is.

If something like this requires large amounts of effort (hours) then I will proceed with my current method but is there some way on this planet to make it easier?

Thanks in advance for any suggestions at all.

-Matt Wilde
matteeboi@gmail.com

Here is a small sample file where I tried to find a variety that might explain the issue

Hi -

your data file has double space (" "), so you can use that in the formula in Column C;

=IFERROR(TRIM(MID(B2,SEARCH("  ",B2)+2,LEN(B2))),"")

event

Awesome!! wow that's helpful. And in the case where the data does not have a double space between name and address?

Hi -

You need to post your data, so everybody can see and offer help.

event

This file has several fields without a double space

Hi -

Are you sure? Why not try the formula for this file and see what will happen?

event

Ya it works pretty well considering the inconsistency of my data actually thank you.

Although whatever the formula copies from column A, remains in that column. Is there any way to copy it from column a then remove it from column A?

Hi -

Copy the data in column A, then use that column A in your formula, paste special values then you can remove the column A

event

event that worked great. Still a couple little bugs here and there because my data is so all over the place but that helped wonders. I'll mark this thread as SOLVED.

Perhaps you may know a solution to another issue? How to select only rows that duplicate more than 20 times?

or rather a filter to show only records that repeat more than 20 times