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.

 

[shaowu459]

Hi, please update your profile of what version Excel you are using, if you are using the latest Office 365, below formula might be helpful.

Book1
	A
1	=100days*(3hrs+0.050weeks)/4
2	=100*(3+0.050)/4
Sheet1

Cell Formulas
Range	Formula
A2	A2	=CONCAT(IFERROR(CHAR(TEXT(CODE(MID(A1,ROW(1:99),1)),"[>122]0;[>96]\@;0")),""))

if you are not using Office 365, maybe an UDF in VBA is the best solution.

 

[rdeguzman]

Hi Shaowu459.
Thanks for your very quick reply! Love and appreciate it!
Unfortunately I do not have Office 365, can you please suggest option formula which can work using Excel 2016. Cheers.

 

[shaowu459]

a boring formula just for your reference

Book2
	A
1	=100days* (3hrs+0.050weeks)/4
2	=100* (3+0.050)/4
Sheet1

Cell Formulas
Range	Formula
A2	A2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"days",""),"hrs",""),"weeks","")

 

[shaowu459]

Book2
	A
1	=100days* (3hrs+0.050weeks)/4
2	=100* (3+0.050)/4
Sheet1

Cell Formulas
Range	Formula
A2	A2	=RMV(A1)

Function RMV(str$)
    With CreateObject("vbscript.regexp")
        .Global = 1
        .Pattern = "[a-zA-Z]"
        If .test(str) Then
            RMV = .Replace(str, "")
        Else
            RMV = str
        End If
    End With
End Function

 

[Peter_SSs]

Unfortunately I do not have Office 365

Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Edit: Oops, Hadn't noticed post #5 posted while I was composing mine.

You could employ a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function NoText(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[A-Za-z]"
    NoText = .Replace(s, "")
  End With
End Function

rdeguzman 2020-05-27 1.xlsm
	A	B
1	=100days*(3hrs+0.050weeks)/4	=100*(3+0.050)/4
Sheet1

Cell Formulas
Range	Formula
B1	B1	=NoText(A1)

 

[sandy666]

You can try Power Query (Get&Transform)

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

 

[sandy666]

or if you want the result of equation

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

 

[rdeguzman]

Thanks for all your reply.
I will try your answers, have not use macro before, but will try to use this until I can fine one using formula only in Excel 2016.
Keep doing what youre doing, really a great help to everyone trying to explore things beyond their comprehension.

 

[sandy666]

You are welcome
Have a nice day