puling a specific price from a list in single cell

[Phill032]

Hi guys,

Hoping someone can help with the below problem
I have a used car stocklist with the price changes made on the internet in the below format, each price is seperated by a "."
What i need is to pull the second last price change.

So my data in column O is - 0. 1099800. 788800. 777700. 699800 - the 0 at the front is the first listing and the are no seperators for cents.
The first actual price is 10,998.00, i just want to pull 7,777.00 being the second last price change.

 

[dave2018]

This may be your answer BUT:

I'm not sure if this is the right way of doing it. I would like to see an expert's solution (unless that's me )

=MID(O1,FIND(".",SUBSTITUTE(SUBSTITUTE(O1,".","%%%"),"%%%",".",LEN(O1)-LEN(SUBSTITUTE(O1,".",""))-1))-(LEN(O1)-LEN(SUBSTITUTE(O1,".","")))+2,6)

2 disclaimers: I'm doing this with the understanding (based on your question) that all these numbers are in 1 cell. namely, O1. Also, This formula pulls out exactly 6 digits from the beginning of the number in question. If your data has larger or smaller number, you will need a slight modification in the end of the formula.

so, if u are still not 100% satisfied, please reply.

 

[Phill032]

Thanks Dave,

You are correct in assuming that these are all in one cell, Doesn't seem to work when i drag it down to other cells. Sometime the "." is at the front of the price and sometimes it is at the back
i get a different answer each time.
I have changed the +2,6 to +2,7 to allow for the extra number and it works on some still but not on all.
Someone provided the below formula a while ago and this works to retrieve the last price so not sure if anyone can maybe convert this for the required outcome.

=MID(LEFT(O2,FIND(".",O2,FIND(".",O2)+1)-1),FIND(".",O2)+1,99)-TRIM(RIGHT(SUBSTITUTE(TRIM(O2)," ",REPT(" ",300)),300))

 

[dave2018]

please give me a few more samples of data (5 should be good) and I will get you that formula!

Edit: Or even better, maybe there could be an Input Cell and you can choose to view the last price by typing a 1, or a 2 for the 2nd to last and so on...

 

[Phill032]

Ok so i have added 5 different cells reflected in each line below.
Might be able to work with an input cell !!
3599900. 3499900. 3399900. 3499900. 3399900. 3299900. 3199900
0.1799900. 1699900. 1599900. 1499900. 1399900
1599900. 1499900. 1399900. 1299900
1599900. 1499900. 1399900. 1599900. 1499900. 1399900. 1299900
999900. 899900

 

[dave2018]

Hi, sorry about the delay... I got way too involved in this subject just now

Here is the formula (I Hope):

=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)),FIND(".",TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)))-1))

About the input cell, I will have to try a little more.

 

[Phill032]

your a genius!!

Works perfectly

 

[dave2018]

Actually, I got the idea from the second half of the formula you provided...

Thanks Dave,

You are correct in assuming that these are all in one cell, Doesn't seem to work when i drag it down to other cells. Sometime the "." is at the front of the price and sometimes it is at the back
i get a different answer each time.
I have changed the +2,6 to +2,7 to allow for the extra number and it works on some still but not on all.
Someone provided the below formula a while ago and this works to retrieve the last price so not sure if anyone can maybe convert this for the required outcome.

=MID(LEFT(O2,FIND(".",O2,FIND(".",O2)+1)-1),FIND(".",O2)+1,99)-TRIM(RIGHT(SUBSTITUTE(TRIM(O2)," ",REPT(" ",300)),300))

that takes the RIGHTMOST charachters (60 in this case) with chunks of spaces, Then takes the LEFTMOST Charchters of that...

 

[Phill032]

For some reason the value i am getting eg 3299900 will not convert to 32999.00. I have tried using curreny and number formats but won't add a separator.
Any ideas?

 

[dave2018]

Are the last 2 charachters always after the decimal?

Edit: Because this might help.. althought i think there might be a shorter way...:

=LEFT(TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)),FIND(".",TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)))-1)),LEN(TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)),FIND(".",TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)))-1)))-2)&"."&RIGHT(TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)),FIND(".",TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)))-1)),2)

Edit 2: or simply add *0.01 to the end of the previous formula. Here is the full thing:

=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)),FIND(".",TRIM(RIGHT(SUBSTITUTE(TRIM(O1)," ",REPT(" ",30)),60)))-1))*0.01