Follow-up... custom delimiter (text to column)
Experts:
In a previous post (https://www.experts-exchange.com/questions/28490280/Custom-delimiter-text-to-column.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
In a previous post (https://www.experts-exchange.com/questions/28490280/Custom-delimiter-text-to-column.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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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)
Unfortunately, Patrick is out of the country now.
Did you get some warning messages on open? Did you click OK (trust this content)
ASKER
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
I'll try on another machine.
Thanks,
EEH
are you on a 64-bit version of Office?
ASKER
Yes... 64-bit OS (Windows 7).
ASKER
Not sure abou6 64-bit version for Office. We're using Office 2010.
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.
You might try opening the xls file without macros enabled and save it as an xlsm format, which supports macros.
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.
@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:
I'm running Office 2010 32-bit (Excel 14.0.7128.5000) on Windows 7 Enterprise SP1 64-bit.
-Glenn
I'm running Office 2010 32-bit (Excel 14.0.7128.5000) on Windows 7 Enterprise SP1 64-bit.
-Glenn
See if the attached xlsm file behaves itself
Regular-Expressions-Examples.xlsm
Regular-Expressions-Examples.xlsm
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
You might want to let Patrick know so the file in the article can be updated.
Thanks,
-Glenn
ASKER
aikimark -- completely agree with Glenn. The xlsm works great!
I'll continue to review the functions.
EEH
I'll continue to review the functions.
EEH
ASKER
Excellent info!!!!
Did you have to tweak the pattern I posted?
Are you now a regular expression enthusiast?
Are you now a regular expression enthusiast?
ASKER
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.
Yes -- I'm converted now... 'will have to learn much more to create own patterns though. :)
Again, thank you for your help.
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.
ASKER
aikimark -- thanks for the additional info... I'll check it out. ;)
EEH
EEH
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