Extract Numbers from Formula to Columns

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
What formula will extract numbers from a formula using any operator as the delimiter?


For example:

Invoice AmountProduct 1Product 2Product 3
=4384+89463+98764384894639876

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

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,194
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,392
also you can try PowerQuery (Get&Transform)

Invoice AmountProduct.1Product.2Product.3
=4384+89463+9876
4384​
89463​
9876​
Code:
[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...
 
Last edited:

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
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

New Member
Joined
Sep 28, 2017
Messages
46
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>

Code:
[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

Well-known Member
Joined
Oct 24, 2015
Messages
5,392
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,392
here is a version for "non-excel" users (based on your example from post#1)

source tableresult
Invoice AmountProduct1Product2Product3Product4Product5Product6Product7Product8Product9Product10
=4384+89463+98764384894639876
=123+5566+4343+789+23112355664343789231
=99+119911
=11+22+33+44+55+66+77+88+88+9911223344556677888899

Code:
[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 :oops:
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

A small UDF would probably do the job ...

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,471
Messages
5,468,820
Members
406,611
Latest member
hanman453

This Week's Hot Topics

Top