List all functions used in an excel formula

[Craigjre]

Hi,

I've been searching everywhere (and perhaps I'm overlooking something really quite simple), but I'm trying to design some code (short of having to create a full formula parsing algorithm) that will take a range and create a list of all excel functions used in that formula. The only two ways I can think of is: 1) Give a full list of excel functions and have VBA loop through each using the InStr function, 2) create a formula parsing algorithm. Both seem long and messy, does anyone know of an easier way to do this?

Thanks in advance for your help,

Craig

[nilem]

Hi Craig, try this

Please Login or Register  to view this content.

[Craigjre]

Oh wow - that is impressive - can you explain to me how it works? What is a regexp object?

Thanks for your help!

[nilem]

In the attached file, select the yellow cell and press the green button.
I have a few links for RegExp, but they are in Russian. Look for examples in this forum.

[jindon]

Should be like this

Please Login or Register  to view this content.

[Craigjre]

Thanks for your replies - what will that pattern pick up, that the original one would not?

Craig

[jindon]

Functions like

CEILING.PRECISE
FLOOR.PRECISE
RANK.EQ
RANK.AVG
NETWORKDAYS.INTL
LOG10
DEC2BIN
BIN2DEC
MODE.MULT
MODE.SNGL

[Craigjre]

Ahh that is very true, thank you so much for your help!!