Extract Numbers from Formula to Columns

[shebe228]

What formula will extract numbers from a formula using any operator as the delimiter?


For example:

[TABLE="width: 550"]
<tbody>[TR]
[TD]Invoice Amount[/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[/TR]
[TR]
[TD]=4384+89463+9876[/TD]
[TD]4384[/TD]
[TD]89463[/TD]
[TD]9876[/TD]
[/TR]
</tbody>[/TABLE]



The invoice amount has a formula that is auto populated from our system and I want to separate the formula into 3 columns. Some lines may have more or less than 3 amounts in the formula.

 

[Joe4]

Not if the Invoice Amount is located in cell A2 ...

Perhaps you should explain your parameters, and what needs to be put in them.
The first one is pretty evident (the cell you want to split), but the second one is not so clear as what should be put in there.
Many people document the parameters at the top of their UDFs (I am big on documenting code!).

 

[James006]

Perhaps you should explain your parameters, and what needs to be put in them.
The first one is pretty evident (the cell you want to split), but the second one is not so clear as what should be put in there.
Many people document the parameters at the top of their UDFs (I am big on documenting code!).

You are so right ... !!!

Function GetElement(Cell As Range, n As Long)
[COLOR=#ff0000][B]' e.g. in cell B2 =GetElement($A$2,COLUMN()-1)[/B][/COLOR]
Dim tmp As String
Dim elm, tSep
tmp = Replace(Cell.Formula, "=", "")
' Replace All potential Operators
For Each tSep In Array("+", "-", "*", "/")
    tmp = Replace(tmp, tSep, "°")
Next tSep
' Split and return Element
elm = Split(tmp, "°")
GetElement = (elm(n - 1))
End Function

 

[Joe4]

Yes, you show an example, but it doesn't describe what each parameter is.
Actually, I think your example may be even more confusing to a user who is not familar with COLUMN()-1 does.
Remember, not everyone asking question here is an expert on writing VBA code, and is going to be able to reverse engineer and figure out what they need to be entering in for each argument.

So maybe something like this:

Function GetElement(Cell As Range, n As Long)
[COLOR=#ff0000]'Parameters
'   Cell:  location of string you want to split
'   n:     which element from string you want to return (i.e. 1 for first, 2 for second, ...)
[/COLOR]'e.g. in cell B2 =GetElement($A$2,COLUMN()-1)[COLOR=#ff0000][/COLOR]
...

 

[Joe4]

BTW, I like your use of COLUMN()-1 in the second argument, so you can just drag that formula across the row to dynamically increment that counter.
But that might just require an explanation for some users, on how that works, especially if the value you are looking up is not in column A (will need to subtract a different number).

 

[James006]

To be on the safe side, much safer to use COLUMNS($A$1:A1) instead of Column()-1