Extracting prices from text string without any expecial order

U

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:

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).
Sort by date Sort by votes
Hi,

This does what you described using your sample data:


Book1
ABCDE
1Galaxy s9 plus a 3.249.900Galaxy s9 plus3.249.9003249900
2Galaxy s9 a 2.899.900Galaxy s92.899.9002899900
3Galaxy note 8 a 2.349.900 doradaGalaxy note 82.349.9002349900
4Galaxy note 8 a 2.389.900 negraGalaxy note 82.389.9002389900
5Galaxy s8 plus a 1.949.900Galaxy s8 plus1.949.9001949900
6Galaxy s8 a 1.749.900Galaxy s81.749.9001749900
7Galaxy a8 plus a 1.479.900Galaxy a8 plus1.479.9001479900
8Galaxy a8 a 1.369.900Galaxy a81.369.9001369900
9Galaxy s7 edge a 1.399.900Galaxy s7 edge1.399.9001399900
10Galaxy s7 a 1.299.900Galaxy s71.299.9001299900
11Iphone x 256gb a 3.679.900Iphone x 256gb3.679.9003679900
12Iphone x 64gb a 3.329.900Iphone x 64gb3.329.9003329900
13Iphone 8 64gb a 2.349.900Iphone 8 64gb2.349.9002349900
14Iphone 8 plus 64gb a 2.679.900Iphone 8 plus 64gb2.679.9002679900
15Iphone 8 plus 256gb a 3.099.900Iphone 8 plus 256gb3.099.9003099900
Sheet31
Cell Formulas
RangeFormula
C1=REPLACE(A1,SEARCH(" a ",A1),255,"")
D1=MID(A1,SEARCH("?.???.???",A1),9)
E1=SUBSTITUTE(MID(A1,SEARCH("?.???.???",A1),9),".","")


For the prices, use Column D formula if you want to retain the decimal symbol, use Column E formula if not.
 
Upvote 0
Yes. Thanks I can wait till tomorrow to try on my PC I’m on my cellphone right now but I guess you did it thank you so much!
Could you please explain in a few words the reasoning behind the formula I don’t know so much about excel but since I’m reading your forums I love it.
 
Last edited:
Upvote 0
Thank you so much the formula worked great.
I found a little problem and its that some prices are not 9 digits but 7.
I should have shown you the whole example.
[TABLE="width: 270"]
<colgroup><col></colgroup><tbody>[TR]
[TD]
Iphone x 256gb a 3.699.900[/TD]
[/TR]
[TR]
[TD]Iphone x 64gb a 3.349.900[/TD]
[/TR]
[TR]
[TD]Iphone 8 64gb a 2.349.900[/TD]
[/TR]
[TR]
[TD]Iphone 8 plus 64gb a 2.699.900[/TD]
[/TR]
[TR]
[TD]Iphone 8 plus 256gb a 3.099.900[/TD]
[/TR]
[TR]
[TD]Iphone 7 32gb a 1.889.900[/TD]
[/TR]
[TR]
[TD]Iphone 7 128gb a 2.049.900[/TD]
[/TR]
[TR]
[TD]IPhone 7 plus 32gb a 2.199.900[/TD]
[/TR]
[TR]
[TD]Iphone 7 plus 128gb a 2.489.900[/TD]
[/TR]
[TR]
[TD]Iphone 6s 16gb a 1.289.900[/TD]
[/TR]
[TR]
[TD]Galaxy note 8 a 2.389.900[/TD]
[/TR]
[TR]
[TD]Galaxy s8 plus a 1.979.900[/TD]
[/TR]
[TR]
[TD]Galaxy s8 a 1.779.900[/TD]
[/TR]
[TR]
[TD]Galaxy s7 edge a 1.449.900[/TD]
[/TR]
[TR]
[TD]Galaxy s7 a 1.329.900[/TD]
[/TR]
[TR]
[TD]Galaxy a7 2017 a 1.099.900[/TD]
[/TR]
[TR]
[TD]Galaxy c9 pro rosa a 1.299.900[/TD]
[/TR]
[TR]
[TD]Galaxy a5 a 999.900[/TD]
[/TR]
[TR]
[TD]Galaxy c5 a 799.900[/TD]
[/TR]
[TR]
[TD]Galaxy c5 pro a 889.900[/TD]
[/TR]
[TR]
[TD]Galaxy c7 a 899.900[/TD]
[/TR]
[TR]
[TD]Galaxy j7 pro a 769.900[/TD]
[/TR]
[TR]
[TD]Galaxy j7 prime a 649.900[/TD]
[/TR]
[TR]
[TD]Galaxy j5 prime a 549.900[/TD]
[/TR]
[TR]
[TD]Gear iconx 2018 a 589.900[/TD]
[/TR]
[TR]
[TD]Xperia xz1 a 1.419.900[/TD]
[/TR]
[TR]
[TD]Xperia xa1 ultra a 899.900[/TD]
[/TR]
[TR]
[TD]HUAWEI MATE 10 a 1.699.900[/TD]
[/TR]
[TR]
[TD]Huawei p10 a 1.299.900[/TD]
[/TR]
[TR]
[TD]Huawei p10 lite a 699.900[/TD]
[/TR]
[TR]
[TD]Huawei Nova a 499.900[/TD]
[/TR]
[TR]
[TD]Xiaomi mi a1 64gb 4ram 649.900[/TD]
[/TR]
[TR]
[TD]Xiaomi mi a1 64gb 4ram 699.900[/TD]
[/TR]
[TR]
[TD]Xiaomi REDMI NOTE 4 64gb 4ram a 599.900[/TD]
[/TR]
[TR]
[TD]Xiaomi redmi note 4 32gb a 499.900[/TD]
[/TR]
[TR]
[TD]Xiaomi redmi note 4x 32gb a 449.900 [/TD]
[/TR]
[TR]
[TD]Xiaomi redmi 4a 32gb 399.900[/TD]
[/TR]
[TR]
[TD]Moto z a 1.149.900[/TD]
[/TR]
[TR]
[TD]Caterpillar s60 a 1.599.900[/TD]
[/TR]
[TR]
[TD]Samsung galaxy a8 plus a 1.499.900[/TD]
[/TR]
[TR]
[TD]SAMSUNG GALAXY J7 PRO a 769.900

is there any solution to this?
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
And Also, some Don't have the common separator " a " that I used in my C column formula...

These should work:


Book1
ABCDE
1Iphone x 256gb a 3.699.900Iphone x 256gb3.699.9003699900
2Iphone x 64gb a 3.349.900Iphone x 64gb3.349.9003349900
3Iphone 8 64gb a 2.349.900Iphone 8 64gb2.349.9002349900
4Iphone 8 plus 64gb a 2.699.900Iphone 8 plus 64gb2.699.9002699900
5Iphone 8 plus 256gb a 3.099.900Iphone 8 plus 256gb3.099.9003099900
6Iphone 7 32gb a 1.889.900Iphone 7 32gb1.889.9001889900
7Iphone 7 128gb a 2.049.900Iphone 7 128gb2.049.9002049900
8IPhone 7 plus 32gb a 2.199.900IPhone 7 plus 32gb2.199.9002199900
9Iphone 7 plus 128gb a 2.489.900Iphone 7 plus 128gb2.489.9002489900
10Iphone 6s 16gb a 1.289.900Iphone 6s 16gb1.289.9001289900
11Galaxy note 8 a 2.389.900Galaxy note 82.389.9002389900
12Galaxy s8 plus a 1.979.900Galaxy s8 plus1.979.9001979900
13Galaxy s8 a 1.779.900Galaxy s81.779.9001779900
14Galaxy s7 edge a 1.449.900Galaxy s7 edge1.449.9001449900
15Galaxy s7 a 1.329.900Galaxy s71.329.9001329900
16Galaxy a7 2017 a 1.099.900Galaxy a7 20171.099.9001099900
17Galaxy c9 pro rosa a 1.299.900Galaxy c9 pro rosa1.299.9001299900
18Galaxy a5 a 999.900 negraGalaxy a5999.900999900
19Galaxy c5 a 799.900Galaxy c5799.900799900
20Galaxy c5 pro a 889.900Galaxy c5 pro889.900889900
21Galaxy c7 a 899.900Galaxy c7899.900899900
22Galaxy j7 pro a 769.900Galaxy j7 pro769.900769900
23Galaxy j7 prime a 649.900Galaxy j7 prime649.900649900
24Galaxy j5 prime a 549.900Galaxy j5 prime549.900549900
25Gear iconx 2018 a 589.900Gear iconx 2018589.900589900
26Xperia xz1 a 1.419.900Xperia xz11.419.9001419900
27Xperia xa1 ultra a 899.900Xperia xa1 ultra899.900899900
28HUAWEI MATE 10 a 1.699.900 whiteHUAWEI MATE 101.699.9001699900
29Huawei p10 a 1.299.900Huawei p101.299.9001299900
30Huawei p10 lite a 699.900Huawei p10 lite699.900699900
31Huawei Nova a 499.900Huawei Nova499.900499900
32Xiaomi mi a1 64gb 4ram 649.900 doradaXiaomi mi a1 64gb 4ram649.900649900
33Xiaomi mi a1 64gb 4ram 699.900Xiaomi mi a1 64gb 4ram699.900699900
34Xiaomi REDMI NOTE 4 64gb 4ram a 599.900Xiaomi REDMI NOTE 4 64gb 4ram599.900599900
35Xiaomi redmi note 4 32gb a 499.900Xiaomi redmi note 4 32gb499.900499900
36Xiaomi redmi note 4x 32gb a 449.900Xiaomi redmi note 4x 32gb449.900449900
37Xiaomi redmi 4a 32gb 399.900Xiaomi redmi 4a 32gb399.900399900
38Moto z 1.149.900Moto z1.149.9001149900
39Caterpillar s60 a 1.599.900Caterpillar s601.599.9001599900
40Samsung galaxy a8 plus a 1.499.900Samsung galaxy a8 plus1.499.9001499900
41SAMSUNG GALAXY J7 PRO a 769.900SAMSUNG GALAXY J7 PRO769.900769900
Sheet31
Cell Formulas
RangeFormula
C1=TRIM(REPLACE(A1,IFERROR(IFERROR(SEARCH(" a ",A1),SEARCH("?.???.???",A1)),SEARCH("???.???",A1)),255,""))
D1=IFERROR(MID(A1,SEARCH("?.???.???",A1),9),MID(A1,SEARCH("???.???",A1),7))
E1=SUBSTITUTE(IFERROR(MID(A1,SEARCH("?.???.???",A1),9),MID(A1,SEARCH("???.???",A1),7)),".","")
 
Upvote 0
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))
 
Upvote 0
István Hirsch said:
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))
Click to expand...

This should also work (normally entered)...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))
 
Upvote 0
You must log in or register to reply here.

Similar threads

2BLUE4U
  • Solved
Extracting Numbers from Text String
2
Replies
10
Views
2K
2BLUE4U
2BLUE4U
G
  • Solved
VBA - Extract numbers from string before specific phrases
Replies
4
Views
1K
gabbi0712
G
T
  • Solved
Extract price from text string
2
Replies
11
Views
3K
Peter_SSs
Peter_SSs
G
  • Question Question
Extract text from string
Replies
3
Views
333
Peter_SSs
Peter_SSs
M
  • Question Question
Extract number with decimal from a text string cell
Replies
2
Views
3K
jtakw
jtakw

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top