Extract Numbers from Formula to Columns

[shebe228]

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


For example:

Invoice Amount	Product 1	Product 2	Product 3
=4384+89463+9876	4384	89463	9876

<tbody>
</tbody>

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)

Invoice Amount		Product.1	Product.2	Product.3
=4384+89463+9876		4384​	89463​	9876​

[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)

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Invoice Amount[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product.1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product.2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product.3[/COLOR]
=4384+89463+9876		4384​	89463​	9876​

<tbody>
</tbody>

[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)

source table		result
Invoice Amount		Product1	Product2	Product3	Product4	Product5	Product6	Product7	Product8	Product9	Product10
=4384+89463+9876		4384	89463	9876
=123+5566+4343+789+231		123	5566	4343	789	231
=99+11		99	11
=11+22+33+44+55+66+77+88+88+99		11	22	33	44	55	66	77	88	88	99

[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 ...