text to columns problem

I need to format a text to column.  the problem is...  i am unable to find a consistent identifier between the two columns except for.

More than one space.........

in column a,  you will see that i can't use spaces because some of the addresses contain more than one space and i can't use fixed width because some of the addresses flow into where the name would be of the customer in the other rows...

i do normally see "2 or more spaces" between the desired column contents.  does anyone know how to do this.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
Use this formula first to replace multi space into single space =TRIM(SUBSTITUTE(A2,CHAR(32)," "))
jamesmetcalf74Author Commented:
question JimJam

if i do that... the addresses with a different number of spaces will become muddied with the customer name...  
am i correct

example  see how the first line will have 4 spaces and then the customer name  while the 2nd will have 3 spaces and then the customer name

124 main st south      lamb, jason         124 main st south lamb,jason
100 elm st          smith, mary                  100 elm st smith, mary
1. Add this function to a module in your VB project and save the workbook as an xlsm (macro-enabled) format.
Function GetSubmatch(ByVal parmText, ByVal parmPattern As String, ByVal parmSubMatch As Long) As String
    'Note: parmSubMatch is one-based for consistency with other Excel and VBA functions
    Static oRE As Object
    Dim oMatches As Object

    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = False
    End If
    If oRE.Pattern <> parmPattern Then
        oRE.Pattern = parmPattern
    End If
    If oRE.test(parmText) Then
        Set oMatches = oRE.Execute(parmText)
        GetSubmatch = oMatches(0).submatches(parmSubMatch - 1)
        GetSubmatch = vbNullString
    End If

End Function

Open in new window

2. In B2, enter the following formula:
=GetSubmatch($A2,"(.*?)  +([^\r]*)",1)

Open in new window

3. in C2, enter the following formula:
=GetSubmatch($A2,"(.*?)  +([^\r]*)",2)

Open in new window

4. fill the formulas down as necessary

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Rob HensonFinance AnalystCommented:
Open in Notepad or similar and do a find and replace for double space and replace with something unique eg pipe symbol ( shift plus key between z and shift). Then the ttc will work using the pipe as the delimiter.
Professor JMicrosoft Excel ExpertCommented:
+1  aikimark  very good approach with regex
Thanks, Prof.  A click on the Good Comment link will also be helpful.
Professor JMicrosoft Excel ExpertCommented:
:-)   just clicked on the "Good Comment"   it is worth 10 clicks, but i just can only click once ;-)
jamesmetcalf74Author Commented:
worked like a charm
thanks everyone
jamesmetcalf74Author Commented:
worked like a charm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.