counting specific characters in a single cell

[Phill032]

Guys is this possible, i know i can use the text to columns feature but i don't have the room on my sheet to do this.
The below is what i have, there is a . between each price change so i want to count the price changes and even possibly sum the difference between the second change and the last if possible.
Would just be happy to solve the first problem to start with..

	0. 1799000. 1699000. 1599000. 1499000. 1399000. 1299000. 1399000. 1399800

<tbody>
</tbody>

 

[BrianJN1]

Guys is this possible, i know i can use the text to columns feature but i don't have the room on my sheet to do this.
The below is what i have, there is a . between each price change so i want to count the price changes and even possibly sum the difference between the second change and the last if possible.
Would just be happy to solve the first problem to start with..

	0. 1799000. 1699000. 1599000. 1499000. 1399000. 1299000. 1399000. 1399800

<tbody>
</tbody>

Interesting Australian Currency.
Can I assume that those values run from Cols A- I?
Those last two? Are they supposed to be both $13.99 or does your pricing actually go out to .001 per unit?

Now is 0 supposed to be the initial price and therefore making 1799000 the first change?

"sum the difference between the second change and the last"
If 169900 is the second change then our difference is 1000000. Going to the last then our difference is 399200.
If my assumptions about Cols is valid you could add to, eg Col J1 this formula: =(B1-C1)+(B1-I1)

As to the first part of your question I am not currently versed enough to answer that.

 

[Rick Rothstein]

Assuming those values are all in cell A1...

Number of Price Changes
------------------------------------------------
=LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

Second Number Minus Last Number
------------------------------------------------
=MID(LEFT(A1,FIND(".",A1,FIND(".",A1)+1)-1),FIND(".",A1)+1,99)-TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",300)),300))


Note: That last formula assumes cell A1 never contain more than 300 total characters in it.

 

[Phill032]

Yeh that works, nice one!!

 

[Peter_SSs]

One example is not a lot to go on but IF it is representative and you want 2nd - last, then this might also do the job for you?
=MID(A1,4,7)-RIGHT(A1,7)
and for the changes:
=(LEN(A1)-1)/9

If, however, the numbers after the initial 0. do not all contain 7 digits, then you could try these for the 2nd-last...
=MID(LEFT(A1,FIND(".",A1,3)),3,20)-RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20)
or
=MID(SUBSTITUTE(A1," ",REPT(" ",20)),20,20)-RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20)

 

[Phill032]

Thanks Peter, will give it a go when i come across a problem where the numbers become <> the 7 digits.

 

[Peter_SSs]

.. will give it a go when i come across a problem where the numbers become <> the 7 digits.

I presume that is referring to my last 2 suggested formulas & implies that currently your numbers are all 7 digits, so my first 2 formulas work for you?