Extract numbers and operators from a cell containing combination of texts, numbers and operators

[rdeguzman]

Hi everyone, appreciate if anyone can help me to create a formula to extract numbers and operators from a cell containing combination of texts, numbers and operators in random sequence.

sample value in cell say =100days* (3hrs+0.050weeks)/4
target result =100*(3+0.050)/4

Appreciate everyone's idea if there can be a formula to solve my query above.
Thanks heaps in advance.

 

[rdeguzman]

or if you want the result of equation
View attachment 14823

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "Result", each Expression.Evaluate(Text.Select([raw],{"0".."9","*","+","/","(",")","."}))),
    TSC = Table.SelectColumns(TS,{"Result"})
in
    TSC

Hi Sandy,
Thanks for your reply, I'm new to Power query - your method can only be used using power query, which means the cell must be in a table and table to be uploaded as query and needs to be refresh to to get the result, is this right?

 

[sandy666]

something like that
but it can be used with named range also

more...

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)