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.

 

[mrshl9898]

Have you tried text to column?

Data tab - Text to Columns - Select Delimited and put a + in the box next to Other, click finish

 

[sandy666]

also you can try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Invoice Amount[/td][td][/td][td=bgcolor:#70AD47]Product.1[/td][td=bgcolor:#70AD47]Product.2[/td][td=bgcolor:#70AD47]Product.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]=4384+89463+9876[/td][td][/td][td=bgcolor:#E2EFDA]

4384​

[/td][td=bgcolor:#E2EFDA]

89463​

[/td][td=bgcolor:#E2EFDA]

9876​

[/td][/tr]
[/table]

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    Rename = Table.RenameColumns(Table.ReplaceValue(Source,"=","",Replacer.ReplaceText,{"Invoice Amount"}),{{"Invoice Amount", "Product"}}),
    Split = Table.SplitColumn(Rename, "Product", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), {"Product.1", "Product.2", "Product.3"}),
    ChType = Table.TransformColumnTypes(Split,{{"Product.1", Int64.Type}, {"Product.2", Int64.Type}, {"Product.3", Int64.Type}})
in
    ChType[/SIZE]

but your example is not representative...

 

[shebe228]

Have you tried text to column?

Data tab - Text to Columns - Select Delimited and put a + in the box next to Other, click finish

Text to columns does work, however I am setting up a template for another department with no excel knowledge.

 

[shebe228]

also you can try PowerQuery (Get&Transform)

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Invoice Amount[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product.1[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product.2[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product.3[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]=4384+89463+9876[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]

4384​

[/TD]
[TD="bgcolor: #E2EFDA"]

89463​

[/TD]
[TD="bgcolor: #E2EFDA"]

9876​

[/TD]
[/TR]
</tbody>[/TABLE]

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    Rename = Table.RenameColumns(Table.ReplaceValue(Source,"=","",Replacer.ReplaceText,{"Invoice Amount"}),{{"Invoice Amount", "Product"}}),
    Split = Table.SplitColumn(Rename, "Product", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), {"Product.1", "Product.2", "Product.3"}),
    ChType = Table.TransformColumnTypes(Split,{{"Product.1", Int64.Type}, {"Product.2", Int64.Type}, {"Product.3", Int64.Type}})
in
    ChType[/SIZE]

but your example is not representative...

It can be assumed "Invoice Amount" is in A1

 

[sandy666]

value or header?
header in A1 so value in A2 of course

these are the tables and the cell address does not matter, you can place/move the tables as you wish

 

[sandy666]

here is a version for "non-excel" users (based on your example from post#1)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]source table[/td][td][/td][td]result[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Invoice Amount[/td][td][/td][td=bgcolor:#70AD47]Product1[/td][td=bgcolor:#70AD47]Product2[/td][td=bgcolor:#70AD47]Product3[/td][td=bgcolor:#70AD47]Product4[/td][td=bgcolor:#70AD47]Product5[/td][td=bgcolor:#70AD47]Product6[/td][td=bgcolor:#70AD47]Product7[/td][td=bgcolor:#70AD47]Product8[/td][td=bgcolor:#70AD47]Product9[/td][td=bgcolor:#70AD47]Product10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]=4384+89463+9876[/td][td][/td][td=bgcolor:#E2EFDA]4384[/td][td=bgcolor:#E2EFDA]89463[/td][td=bgcolor:#E2EFDA]9876[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]=123+5566+4343+789+231[/td][td][/td][td]123[/td][td]5566[/td][td]4343[/td][td]789[/td][td]231[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]=99+11[/td][td][/td][td=bgcolor:#E2EFDA]99[/td][td=bgcolor:#E2EFDA]11[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]=11+22+33+44+55+66+77+88+88+99[/td][td][/td][td]11[/td][td]22[/td][td]33[/td][td]44[/td][td]55[/td][td]66[/td][td]77[/td][td]88[/td][td]88[/td][td]99[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="[B][COLOR="#FF0000"]Table1[/COLOR][/B]"]}[Content],
    Replace = Table.ReplaceValue(Source,"=","",Replacer.ReplaceText,{"Invoice Amount"}),
    SplitCount = Table.AddColumn(Replace, "Split Count", each List.Count(Text.Split([Invoice Amount],"+"))),
    MaxCount = List.Max(SplitCount[Split Count]),
    List = List.Transform({1..MaxCount}, each "Product"&Text.From(_)),
    SplitPlus = Table.SplitColumn(SplitCount,"Invoice Amount",Splitter.SplitTextByDelimiter("+"), List),
    RC = Table.RemoveColumns(SplitPlus,{"Split Count"})
in
    RC[/SIZE]

user paste data into source table (blue) then refresh result table (green) (Ctrl+Alt+F5 or just right click on green table and select Refresh)

change the name of source table suitably (red in M-code)

edit:
PowerQuery (Get&Transform) is required

and don't quote whole post, please

 

[James006]

Hello,

A small UDF would probably do the job ...

Function GetElement(Cell As Range, n As Long)
' e.g. in cell B2 =GetElement($A$2,COLUMN()-1)
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

Hope this will help

 

[shebe228]

That UDF produces an error.

 

[James006]

That UDF produces an error.

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