?
Solved

Follow-up... custom delimiter (text to column)

Posted on 2014-08-05
20
Medium Priority
?
233 Views
Last Modified: 2014-08-06
Experts:

In a previous post (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28490280.html ), I've was provided an excellent solution for developing custom-delimiter formulas/functions.

Upon receiving the first (excellent) solution, I closed the question and awarded points to expert (Glenn).
Afterwards, expert (aikimark) offered yet another solution... which is also very efficient.

At this time, I'd like to learn more about the regex pattern so that a) I can make sense of its logic and b) modify it in the event additional pattern recognition is required.

Thank you in advance for any "comments" you could offer on aikimark's VBA.

EEH
0
Comment
Question by:ExpExchHelp
  • 9
  • 9
  • 2
20 Comments
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 40241204
I assume you are playing with http:Q_28490280.html#a40239901
Correct me if I'm wrong.

Here is a copy of the code, so we don't have to switch between threads
Sub Q_28490280()
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim rng As Range
    Dim rngCell As Range
    
    Set rng = ActiveSheet.Range(ActiveSheet.Range("A2"), ActiveSheet.Range("A2").End(xlDown))
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = False
    oRE.Pattern = "(.*?) (U[A-Z]{1,3}[^<]*)(<[^>]+>)"
    'NOTE: uncomment the following statement for best performance
    'Application.ScreenUpdating = False
    For Each rngCell In rng
        If oRE.test(rngCell.Value) Then
            Set oMatches = oRE.Execute(rngCell.Value)
            With oMatches(0)
                rngCell.Offset(0, 1).Value = .submatches(0)
                rngCell.Offset(0, 2).Value = .submatches(1)
                rngCell.Offset(0, 3).Value = .submatches(2)
                'Debug.Print .submatches(0), .submatches(1), .submatches(2)
            End With
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

Firstly, you should familiarize yourself with regular expressions in the VBA environment.  A good start is this article: http:A_1336.html

The Pattern:
(.*?) (U[A-Z]{1,3}[^<]*)(<[^>]+>)
"(.*?)" - capture zero or more characters, but don't be greedy
" " - there will be a space character, but don't capture it
"(U[A-Z]{1,3}[^<]*)" - capture a string that starts with an upper case U and is followed by 1-3 upper case letters.  That will be followed by zero or more characters that are not a less than character, <
"(<[^>]+>)" - capture a string that begins with a less than character, <, followed by one or more characters that are not a greater than character, >, terminated with a greater than character.

Thus, there are three capture groups which result in three submatches items.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40241214
You only provided three data examples, which had been sanitized and your data description was not detailed enough that I could have posted a more refined regex pattern.  We can use this thread for that refinement.

I think this approach is better than trying to type all the possible combinations of delimiters for the formula solution you accepted in the prior related thread
0
 

Author Comment

by:ExpExchHelp
ID: 40241239
aikimark:

Thank you for the prompt response and additional information.

As suggested, allow me to familiarize myself with the article posted on http:A_1336.html.

MTF,
EEH
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ExpExchHelp
ID: 40241324
aikimark:

As part of Patrick's posting, he provided an XLS with example solution.

I've downloaded the file twice... and twice it results in errors.    Do you know what might be causing the VBA errors?   Please see attached Patrick's XLS as well as two screenshots (errors that I run into when opening the spreadsheet).

EEH
Regular-Expressions-Examples.xls
Capture.JPG
Capture2.JPG
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40241357
I can open the workbook.  Does it fail on open or when you have tried to do something in particular?

Unfortunately, Patrick is out of the country now.

Did you get some warning messages on open?  Did you click OK (trust this content)
0
 

Author Comment

by:ExpExchHelp
ID: 40241399
It pretty much freezes (resulting in error as shown in snaphot 1).    Once I attempt closing it (which takes a moment), I then see the VBA error message (400)... snapshot 2.

I'll try on another machine.

Thanks,
EEH
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40241435
are you on a 64-bit version of Office?
0
 

Author Comment

by:ExpExchHelp
ID: 40241486
Yes... 64-bit OS (Windows 7).
0
 

Author Comment

by:ExpExchHelp
ID: 40241496
Not sure abou6 64-bit version for Office.   We're using Office 2010.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40241558
It should show up in the Help About.

You might try opening the xls file without macros enabled and save it as an xlsm format, which supports macros.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40241708
If you have Excel2003 with the compatibility pack installed, you should be able to open the xls file and save it as an xlsm file.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40241722
@aikimark..I tried opening the Patrick's example file and received the same errors - Excel forced to restart.  On Excel restart I receive the following:
Excel Error

I'm running Office 2010 32-bit (Excel 14.0.7128.5000) on Windows 7 Enterprise SP1 64-bit.  

-Glenn
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40242407
See if the attached xlsm file behaves itself
Regular-Expressions-Examples.xlsm
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40242429
Yep! Works like a charm (great functions).

You might want to let Patrick know so the file in the article can be updated.

Thanks,
-Glenn
0
 

Author Comment

by:ExpExchHelp
ID: 40243510
aikimark -- completely agree with Glenn.   The xlsm works great!  

I'll continue to review the functions.

EEH
0
 

Author Closing Comment

by:ExpExchHelp
ID: 40243516
Excellent info!!!!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40243573
Did you have to tweak the pattern I posted?

Are you now a regular expression enthusiast?
0
 

Author Comment

by:ExpExchHelp
ID: 40244064
No -- didn't have to tweak your (excellent) solution.   :)
Yes -- I'm converted now... 'will have to learn much more to create own patterns though.   :)

Again, thank you for your help.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40244253
In your original sample data, all the organization names seemed to be terminated with a "(XX)" string.  If that parenthetical string exists for all your records, no matter the length of that string, you can trim the parsed organization name with a tweaked pattern that terminates the organization name with that pattern.
0
 

Author Comment

by:ExpExchHelp
ID: 40244691
aikimark -- thanks for the additional info... I'll check it out.   ;)

EEH
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question