Regular expressions in Excel X

[Steven Sartorius]

I have a spreadsheet that was developed on WinXL that has a VB function that
calls the Microsoft Vbscript Regular Expression library. Is such a beast
available for Excel X on the Mac or do I need to roll my own regular
expression support??

Thanks for any help,

Steve

 

[J.E. McGimpsey]

I have a spreadsheet that was developed on WinXL that has a VB function that
calls the Microsoft Vbscript Regular Expression library. Is such a beast
available for Excel X on the Mac or do I need to roll my own regular
expression support??

You'll need to roll your own, but you may be able to do it fairly
easily via Applescript (MacScript() in VBA).

With Applescript 1.8, you can use the "do shell script" command to
use the Unix sh shell commands which use basic or extended regexps
(e.g., egrep, sed, awk). You may find these URLs helpful:

http://developer.apple.com/technotes/tn2002/tn2065.html

http://www.macosx.org/software/utilities/unix_applescripting.html

 

[Corentin Cras-Méneur [MVP]]

You'll need to roll your own, but you may be able to do it fairly
easily via Applescript (MacScript() in VBA).

With Applescript 1.8, you can use the "do shell script" command to
use the Unix sh shell commands which use basic or extended regexps
(e.g., egrep, sed, awk). You may find these URLs helpful:

Quite true!! but it still is a lot more complicated than if we had
RegEx directly in Excel. Especially when you use it all the time.
Developping a script for a simple search and replace that you will only
use once seems a little overkill for me :-\

I really wish we had perl-like regex in Excel (and Word) :-|
Excerl does support a few (very few, fewer than Word) wildcars, but so
far I found it easier (and faster) to copy the data in BBEdit, make the
modifications and paste it back in Excel.

Oh well,



Corentin

 

[J.E. McGimpsey]

Quite true!! but it still is a lot more complicated than if we had
RegEx directly in Excel. Especially when you use it all the time.
Developping a script for a simple search and replace that you will only
use once seems a little overkill for me :-\

I really wish we had perl-like regex in Excel (and Word) :-|
Excerl does support a few (very few, fewer than Word) wildcars, but so
far I found it easier (and faster) to copy the data in BBEdit, make the
modifications and paste it back in Excel.

Well, I wouldn't recommend a script for a one-off...

If you use regexes all the time, and consistently, I'd think a
script that could pass the text to sed, or even Perl, would be
faster than copying and pasting. OTOH, I haven't built the engine,
so I could be wrong.

It's been a long time since I wrote a .dll, but providing regex
support might be fun to implement.

I wouldn't hold my breath for XL to add regex support directly

 

[Corentin Cras-Méneur [MVP]]

Well, I wouldn't recommend a script for a one-off...

Unfortunately. Much too tedious (especially for me since you know the
extent of my VBA knowledge ;-))) ).

If you use regexes all the time, and consistently, I'd think a
script that could pass the text to sed, or even Perl, would be
faster than copying and pasting. OTOH, I haven't built the engine,
so I could be wrong.

That's an option, though copy paste is not that much of a hassle and
handling regex through BBEdit is convenienet since it can let me access
to a wide library of regex patterns I previously used and let me modify
them to suit my needs.

It's been a long time since I wrote a .dll, but providing regex
support might be fun to implement.

) I sure would find some use for it ;-))))))

I wouldn't hold my breath for XL to add regex support directly

Yeah :-\
It would be a killer feature though (as far as I'm concerned).

Corentin