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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mrshl9898

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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,130,009
Messages
5,639,530
Members
417,094
Latest member
IsimiKehinde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top