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:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

This does what you described using your sample data:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Galaxy s9 plus a 3.249.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s9 plus</td><td style=";">3.249.900</td><td style="text-align: right;;">3249900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Galaxy s9 a 2.899.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s9</td><td style=";">2.899.900</td><td style="text-align: right;;">2899900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Galaxy note 8 a 2.349.900 dorada</td><td style="text-align: right;;"></td><td style=";">Galaxy note 8</td><td style=";">2.349.900</td><td style="text-align: right;;">2349900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Galaxy note 8 a 2.389.900 negra</td><td style="text-align: right;;"></td><td style=";">Galaxy note 8</td><td style=";">2.389.900</td><td style="text-align: right;;">2389900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Galaxy s8 plus a 1.949.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s8 plus</td><td style=";">1.949.900</td><td style="text-align: right;;">1949900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Galaxy s8 a 1.749.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s8</td><td style=";">1.749.900</td><td style="text-align: right;;">1749900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Galaxy a8 plus a 1.479.900</td><td style="text-align: right;;"></td><td style=";">Galaxy a8 plus</td><td style=";">1.479.900</td><td style="text-align: right;;">1479900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Galaxy a8 a 1.369.900</td><td style="text-align: right;;"></td><td style=";">Galaxy a8</td><td style=";">1.369.900</td><td style="text-align: right;;">1369900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Galaxy s7 edge a 1.399.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s7 edge</td><td style=";">1.399.900</td><td style="text-align: right;;">1399900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Galaxy s7 a 1.299.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s7</td><td style=";">1.299.900</td><td style="text-align: right;;">1299900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Iphone x 256gb a 3.679.900</td><td style="text-align: right;;"></td><td style=";">Iphone x 256gb</td><td style=";">3.679.900</td><td style="text-align: right;;">3679900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Iphone x 64gb a 3.329.900</td><td style="text-align: right;;"></td><td style=";">Iphone x 64gb</td><td style=";">3.329.900</td><td style="text-align: right;;">3329900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Iphone 8 64gb a 2.349.900</td><td style="text-align: right;;"></td><td style=";">Iphone 8 64gb</td><td style=";">2.349.900</td><td style="text-align: right;;">2349900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Iphone 8 plus 64gb a 2.679.900</td><td style="text-align: right;;"></td><td style=";">Iphone 8 plus 64gb</td><td style=";">2.679.900</td><td style="text-align: right;;">2679900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Iphone 8 plus 256gb a 3.099.900</td><td style="text-align: right;;"></td><td style=";">Iphone 8 plus 256gb</td><td style=";">3.099.900</td><td style="text-align: right;;">3099900</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet31</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=REPLACE(<font color="Blue">A1,SEARCH(<font color="Red">" a ",A1</font>),255,""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=MID(<font color="Blue">A1,SEARCH(<font color="Red">"?.???.???",A1</font>),9</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">MID(<font color="Red">A1,SEARCH(<font color="Green">"?.???.???",A1</font>),9</font>),".",""</font>)</td></tr></tbody></table></td></tr></table><br />

For the prices, use Column D formula if you want to retain the decimal symbol, use Column E formula if not.
 

Uriakus

New Member
Joined
Nov 9, 2014
Messages
13
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:

Uriakus

New Member
Joined
Nov 9, 2014
Messages
13
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.

Iphone x 256gb a 3.699.900
Iphone x 64gb a 3.349.900
Iphone 8 64gb a 2.349.900
Iphone 8 plus 64gb a 2.699.900
Iphone 8 plus 256gb a 3.099.900
Iphone 7 32gb a 1.889.900
Iphone 7 128gb a 2.049.900
IPhone 7 plus 32gb a 2.199.900
Iphone 7 plus 128gb a 2.489.900
Iphone 6s 16gb a 1.289.900
Galaxy note 8 a 2.389.900
Galaxy s8 plus a 1.979.900
Galaxy s8 a 1.779.900
Galaxy s7 edge a 1.449.900
Galaxy s7 a 1.329.900
Galaxy a7 2017 a 1.099.900
Galaxy c9 pro rosa a 1.299.900
Galaxy a5 a 999.900
Galaxy c5 a 799.900
Galaxy c5 pro a 889.900
Galaxy c7 a 899.900
Galaxy j7 pro a 769.900
Galaxy j7 prime a 649.900
Galaxy j5 prime a 549.900
Gear iconx 2018 a 589.900
Xperia xz1 a 1.419.900
Xperia xa1 ultra a 899.900
HUAWEI MATE 10 a 1.699.900
Huawei p10 a 1.299.900
Huawei p10 lite a 699.900
Huawei Nova a 499.900
Xiaomi mi a1 64gb 4ram 649.900
Xiaomi mi a1 64gb 4ram 699.900
Xiaomi REDMI NOTE 4 64gb 4ram a 599.900
Xiaomi redmi note 4 32gb a 499.900
Xiaomi redmi note 4x 32gb a 449.900
Xiaomi redmi 4a 32gb 399.900
Moto z a 1.149.900
Caterpillar s60 a 1.599.900
Samsung galaxy a8 plus a 1.499.900
SAMSUNG GALAXY J7 PRO a 769.900

is there any solution to this?

<colgroup><col></colgroup><tbody>
</tbody>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

And Also, some Don't have the common separator " a " that I used in my C column formula...

These should work:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Iphone x 256gb a 3.699.900</td><td style="text-align: right;;"></td><td style=";">Iphone x 256gb</td><td style=";">3.699.900</td><td style="text-align: right;;">3699900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Iphone x 64gb a 3.349.900</td><td style="text-align: right;;"></td><td style=";">Iphone x 64gb</td><td style=";">3.349.900</td><td style="text-align: right;;">3349900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Iphone 8 64gb a 2.349.900</td><td style="text-align: right;;"></td><td style=";">Iphone 8 64gb</td><td style=";">2.349.900</td><td style="text-align: right;;">2349900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Iphone 8 plus 64gb a 2.699.900</td><td style="text-align: right;;"></td><td style=";">Iphone 8 plus 64gb</td><td style=";">2.699.900</td><td style="text-align: right;;">2699900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Iphone 8 plus 256gb a 3.099.900</td><td style="text-align: right;;"></td><td style=";">Iphone 8 plus 256gb</td><td style=";">3.099.900</td><td style="text-align: right;;">3099900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Iphone 7 32gb a 1.889.900</td><td style="text-align: right;;"></td><td style=";">Iphone 7 32gb</td><td style=";">1.889.900</td><td style="text-align: right;;">1889900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Iphone 7 128gb a 2.049.900</td><td style="text-align: right;;"></td><td style=";">Iphone 7 128gb</td><td style=";">2.049.900</td><td style="text-align: right;;">2049900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">IPhone 7 plus 32gb a 2.199.900</td><td style="text-align: right;;"></td><td style=";">IPhone 7 plus 32gb</td><td style=";">2.199.900</td><td style="text-align: right;;">2199900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Iphone 7 plus 128gb a 2.489.900</td><td style="text-align: right;;"></td><td style=";">Iphone 7 plus 128gb</td><td style=";">2.489.900</td><td style="text-align: right;;">2489900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Iphone 6s 16gb a 1.289.900</td><td style="text-align: right;;"></td><td style=";">Iphone 6s 16gb</td><td style=";">1.289.900</td><td style="text-align: right;;">1289900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Galaxy note 8 a 2.389.900</td><td style="text-align: right;;"></td><td style=";">Galaxy note 8</td><td style=";">2.389.900</td><td style="text-align: right;;">2389900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Galaxy s8 plus a 1.979.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s8 plus</td><td style=";">1.979.900</td><td style="text-align: right;;">1979900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Galaxy s8 a 1.779.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s8</td><td style=";">1.779.900</td><td style="text-align: right;;">1779900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Galaxy s7 edge a 1.449.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s7 edge</td><td style=";">1.449.900</td><td style="text-align: right;;">1449900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Galaxy s7 a 1.329.900</td><td style="text-align: right;;"></td><td style=";">Galaxy s7</td><td style=";">1.329.900</td><td style="text-align: right;;">1329900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Galaxy a7 2017 a 1.099.900</td><td style="text-align: right;;"></td><td style=";">Galaxy a7 2017</td><td style=";">1.099.900</td><td style="text-align: right;;">1099900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Galaxy c9 pro rosa a 1.299.900</td><td style="text-align: right;;"></td><td style=";">Galaxy c9 pro rosa</td><td style=";">1.299.900</td><td style="text-align: right;;">1299900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Galaxy a5 a 999.900 negra</td><td style="text-align: right;;"></td><td style=";">Galaxy a5</td><td style="text-align: right;;">999.900</td><td style="text-align: right;;">999900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Galaxy c5 a 799.900</td><td style="text-align: right;;"></td><td style=";">Galaxy c5</td><td style="text-align: right;;">799.900</td><td style="text-align: right;;">799900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Galaxy c5 pro a 889.900</td><td style="text-align: right;;"></td><td style=";">Galaxy c5 pro</td><td style="text-align: right;;">889.900</td><td style="text-align: right;;">889900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Galaxy c7 a 899.900</td><td style="text-align: right;;"></td><td style=";">Galaxy c7</td><td style="text-align: right;;">899.900</td><td style="text-align: right;;">899900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Galaxy j7 pro a 769.900</td><td style="text-align: right;;"></td><td style=";">Galaxy j7 pro</td><td style="text-align: right;;">769.900</td><td style="text-align: right;;">769900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">Galaxy j7 prime a 649.900</td><td style="text-align: right;;"></td><td style=";">Galaxy j7 prime</td><td style="text-align: right;;">649.900</td><td style="text-align: right;;">649900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">Galaxy j5 prime a 549.900</td><td style="text-align: right;;"></td><td style=";">Galaxy j5 prime</td><td style="text-align: right;;">549.900</td><td style="text-align: right;;">549900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">Gear iconx 2018 a 589.900</td><td style="text-align: right;;"></td><td style=";">Gear iconx 2018</td><td style="text-align: right;;">589.900</td><td style="text-align: right;;">589900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">Xperia xz1 a 1.419.900</td><td style="text-align: right;;"></td><td style=";">Xperia xz1</td><td style=";">1.419.900</td><td style="text-align: right;;">1419900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">Xperia xa1 ultra a 899.900</td><td style="text-align: right;;"></td><td style=";">Xperia xa1 ultra</td><td style="text-align: right;;">899.900</td><td style="text-align: right;;">899900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">HUAWEI MATE 10 a 1.699.900 white</td><td style="text-align: right;;"></td><td style=";">HUAWEI MATE 10</td><td style=";">1.699.900</td><td style="text-align: right;;">1699900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">Huawei p10 a 1.299.900</td><td style="text-align: right;;"></td><td style=";">Huawei p10</td><td style=";">1.299.900</td><td style="text-align: right;;">1299900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">Huawei p10 lite a 699.900</td><td style="text-align: right;;"></td><td style=";">Huawei p10 lite</td><td style="text-align: right;;">699.900</td><td style="text-align: right;;">699900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style=";">Huawei Nova a 499.900</td><td style="text-align: right;;"></td><td style=";">Huawei Nova</td><td style="text-align: right;;">499.900</td><td style="text-align: right;;">499900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style=";">Xiaomi mi a1 64gb 4ram 649.900 dorada</td><td style="text-align: right;;"></td><td style=";">Xiaomi mi a1 64gb 4ram</td><td style="text-align: right;;">649.900</td><td style="text-align: right;;">649900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style=";">Xiaomi mi a1 64gb 4ram 699.900</td><td style="text-align: right;;"></td><td style=";">Xiaomi mi a1 64gb 4ram</td><td style="text-align: right;;">699.900</td><td style="text-align: right;;">699900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style=";">Xiaomi REDMI NOTE 4 64gb 4ram a 599.900</td><td style="text-align: right;;"></td><td style=";">Xiaomi REDMI NOTE 4 64gb 4ram</td><td style="text-align: right;;">599.900</td><td style="text-align: right;;">599900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style=";">Xiaomi redmi note 4 32gb a 499.900</td><td style="text-align: right;;"></td><td style=";">Xiaomi redmi note 4 32gb</td><td style="text-align: right;;">499.900</td><td style="text-align: right;;">499900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style=";">Xiaomi redmi note 4x 32gb a 449.900</td><td style="text-align: right;;"></td><td style=";">Xiaomi redmi note 4x 32gb</td><td style="text-align: right;;">449.900</td><td style="text-align: right;;">449900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">37</td><td style=";">Xiaomi redmi 4a 32gb 399.900</td><td style="text-align: right;;"></td><td style=";">Xiaomi redmi 4a 32gb</td><td style="text-align: right;;">399.900</td><td style="text-align: right;;">399900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">38</td><td style=";">Moto z 1.149.900</td><td style="text-align: right;;"></td><td style=";">Moto z</td><td style=";">1.149.900</td><td style="text-align: right;;">1149900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">39</td><td style=";">Caterpillar s60 a 1.599.900</td><td style="text-align: right;;"></td><td style=";">Caterpillar s60</td><td style=";">1.599.900</td><td style="text-align: right;;">1599900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">40</td><td style=";">Samsung galaxy a8 plus a 1.499.900</td><td style="text-align: right;;"></td><td style=";">Samsung galaxy a8 plus</td><td style=";">1.499.900</td><td style="text-align: right;;">1499900</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">41</td><td style=";">SAMSUNG GALAXY J7 PRO a 769.900</td><td style="text-align: right;;"></td><td style=";">SAMSUNG GALAXY J7 PRO</td><td style="text-align: right;;">769.900</td><td style="text-align: right;;">769900</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet31</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=TRIM(<font color="Blue">REPLACE(<font color="Red">A1,IFERROR(<font color="Green">IFERROR(<font color="Purple">SEARCH(<font color="Teal">" a ",A1</font>),SEARCH(<font color="Teal">"?.???.???",A1</font>)</font>),SEARCH(<font color="Purple">"???.???",A1</font>)</font>),255,""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=IFERROR(<font color="Blue">MID(<font color="Red">A1,SEARCH(<font color="Green">"?.???.???",A1</font>),9</font>),MID(<font color="Red">A1,SEARCH(<font color="Green">"???.???",A1</font>),7</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">IFERROR(<font color="Red">MID(<font color="Green">A1,SEARCH(<font color="Purple">"?.???.???",A1</font>),9</font>),MID(<font color="Green">A1,SEARCH(<font color="Purple">"???.???",A1</font>),7</font>)</font>),".",""</font>)</td></tr></tbody></table></td></tr></table><br />
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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))

This should also work (normally entered)...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))
 

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,596
Members
410,623
Latest member
RusHartley
Top