Extract number with decimals from cell

[bukimi]

Hello,

Sometimes I need to extract prices from cell contents. The problem is that some people leave some empty spaces or words in cell, so I can't calculate with these values.
So, I may have "15,44" or " 15,44 " or "price 15,44" or a mix of it.

There are some solutions and formulas to extract numbers from a cell, like this one:

Public Function GetNumericValue(range)
    Set myRegExp = New RegExp
    myRegExp.IgnoreCase = True
    myRegExp.Global = True
    myRegExp.Pattern = "[\D]"

    GetNumericValue = myRegExp.Replace(range.Value, "")
End Function

but it ignores decimal values separator. In my country it's "," (comma) if it matters.
Therefore, all solutions I tried so far would give me "1544" as a price (instead of 15,44) from example problematic cells.

What else can I try?

Thank you in advance for all your help!

 

[JimM]

Assuming your data starts in A1 does this work ?

=--MID(A1,AGGREGATE(15,6,FIND(ROW($1:$10)-1,A1),1),AGGREGATE(14,6,FIND(ROW($1:$10)-1,A1),1)-AGGREGATE(15,6,FIND(ROW($1:$10)-1,A1),1)+1)

 

[sandy666]

with Power Query

raw	value
price 15,44	15,44
15,44	15,44
15,44	15,44

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "value", each Text.Select([raw],{"0".."9",","}))
in
    TS

 

[bukimi]

Assuming your data starts in A1 does this work ?

=--MID(A1,AGGREGATE(15,6,FIND(ROW($1:$10)-1,A1),1),AGGREGATE(14,6,FIND(ROW($1:$10)-1,A1),1)-AGGREGATE(15,6,FIND(ROW($1:$10)-1,A1),1)+1)

I need it to work quite flexible for any range in any direction. Therefore, I don't want to be limited to referencing to problematic cell in another cell to get result.

with Power Query

I don't have PowerQuery Addon and won't have as it's my company's computer I can't install anything on...

 

[sandy666]

what is your Excel version ?
update your profile about it

 

[bukimi]

It's Excel 2016.
Sorry, I'll update it right away. I had update from 2013 version recently.

 

[sandy666]

XL2016 has Power Query (Get&Transform) built-in

 

[bukimi]

XL2016 has Power Query (Get&Transform) built-in

Oh, I didn't know that! Thank you.
I don't know how to use it unfortunately. I'll google some tutorials for it. I even don't know where to put your Power Query code you provided.
I know only about writing/using VBA macros.

 

[sandy666]

more about PowerQuery aka Get&Transform

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

select column with data, use Ctrl+T
then Data tab - From Table (it will open Power Query Editor) - Advanced editor, replace all code there with copied code from here

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    TS = Table.AddColumn(Text, "value", each Text.Trim(Text.Select([raw],{"0".."9",","}),",")),
    TSC = Table.SelectColumns(TS,{"value"})
in
    TSC

make sure the name of the source table is the same as in the code (here: Table1)

raw		value
price 15,44		15,44
15,44		15,44
15,44		15,44
ad, c ,,, 15,44		15,44

 

[bukimi]

I was getting error until I noticed that my column in the table also has to be named "raw"

It works! After accepting changes, I got this table in a new Sheet transformed to numbers with decimals only. Thank you!
Still, it looks a little slow with so many clicking around and now works only for a specified column (not a whole range). VBA seems so much faster than PowerQuery for this.