Extracting prices from text string without any expecial order

Uriakus

New Member
Joined
Nov 9, 2014
Messages
13
Hi, I’ve been searching in the forums but without any luck I’ve seen very beautiful fórmulas for extracting numbers from text strings but in my case I only need to extract the price from a list of products which names sometimes has numbers inside them and I only need the prices from those cells.

Let me show you.

Here is column A...

Galaxy s9 plus a 3.249.900
Galaxy s9 a 2.899.900
Galaxy note 8 a 2.349.900 dorada
Galaxy note 8 a 2.389.900 negra
Galaxy s8 plus a 1.949.900
Galaxy s8 a 1.749.900
Galaxy a8 plus a 1.479.900
Galaxy a8 a 1.369.900
Galaxy s7 edge a 1.399.900
Galaxy s7 a 1.299.900
Iphone x 256gb a 3.679.900
Iphone x 64gb a 3.329.900
Iphone 8 64gb a 2.349.900
Iphone 8 plus 64gb a 2.679.900
Iphone 8 plus 256gb a 3.099.900

I’ve tried text to columns but doesn’t exactly do what I need.
I’ve used the left function but when there is a text in the last part of the cell it doesn’t return the numbers.
So I would like a formula that could take exactly the price ignoring numbers like the one that are on the model’s names...
For example iPhone 8 64GB has 3 numbers in its name and I don’t want the formula to show them.
The formulas that I saw took all the numbers from a cell and I only need the price from that cell.
As you can see my prices are big... 3.249.900
In another cases there’s a final text string in the cell in this case the third and forth row... Dorada, Negra.
I tried text to columns but all prices are not align in the same column I used “a” as a parameter but in some of my products there are “a” in the names and I used spaces as another parameter but as I mentioned they didn’t finish on the same column.
In conclusion...
I want the product name on one column and the price in another column. If you see errors in my sintaxis is because I don’t speak English. Any help would be much appreciated.

This is the result I’m hoping...

Samsung S9 Plus 3249900
Galaxy S9 2899900
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,417
Office Version
  1. 2010
Platform
  1. Windows
Or give this a try (enter with Ctrl - Shift - Enter, not just Enter)

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,MATCH(2,1/(1+MID(A1,ROW($1:$40),1))))," ", REPT(" ",20)),20))
Here is a normally entered formula that looks like it works...

=TRIM(RIGHT(SUBSTITUTE(REPLACE(A1,FIND("."&TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99)),A1)+4,99,"")," ",REPT(" ",99)),99))
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Uriakus

New Member
Joined
Nov 9, 2014
Messages
13
Thanks to all of you is amazing how you just do this. You don't imagine how happy I am.
I'm 41 and very busy but believe me when I tell you I'm gonna start learning excel and watching all of your posts. It's just beautiful how this works thank you guys and keep the good work.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,798
Members
410,708
Latest member
SanTrapGamer
Top