text to columns problem

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
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.
experts-exchange-problem-2.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Use this formula first to replace multi space into single space =TRIM(SUBSTITUTE(A2,CHAR(32)," "))

Author

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
Top Expert 2014
Commented:
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)
    Else
        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-problem-2.xlsm
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Rob HensonFinance Analyst

Commented:
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 Expert
Top Expert 2014

Commented:
+1  aikimark  very good approach with regex
Top Expert 2014

Commented:
Thanks, Prof.  A click on the Good Comment link will also be helpful.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
:-)   just clicked on the "Good Comment"   it is worth 10 clicks, but i just can only click once ;-)

Author

Commented:
worked like a charm
thanks everyone

Author

Commented:
worked like a charm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial