find position of last . in a cell

[hans]

I need to split a cell into two parts.
I need to find the position of the last . in a cell

can someone tel me how to do this?

greetings Hans

 

[Peo Sjoblom]

One way

=LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

 

[Peo Sjoblom]

Oops!

=FIND("^^",SUBSTITUTE(A1,".","^^",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))

 

[Jason Morin]

=FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LEN(SUBSTITUTE
(A1,".",""))))

HTH
Jason
Atlanta, GA

 

[Ron de Bruin]

<vbg>

 

[immanuel]

Or with an array formula:

=MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=".",ROW(INDIRECT("1:"&LEN(A1))
,0)

/i.

 

[Chris Leonard]

=FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LEN(SUBSTITUTE

(A1,".",""))))

What does the "^" mean please ?

I can't see how this works, but it does!!

Chris

 

[Harald Staff]

Hi Chris

It's just a nonsense sign that replaces the very last . in a virtual model of the cell
value. Which makes it unique so you can do a full search and find it's exact position. If
^ is likely already there then use something else.

 

[Jason Morin]

It doesn't mean anything. The last "." in your cell is
being replaced by a "^", and then the FIND locates
the "^". "^" is an odd character and chances are it won't
be in your text string. If there is one, the formula won't
work. Peo used "^^", which is a better. Heck, you could
use #@$%^*! if you wanted to.

HTH
Jason
Atlanta, GA

 

[Harlan Grove]

I need to split a cell into two parts.
I need to find the position of the last . in a cell

Yet another alternative. If your text were in cell A4, try the following array
formula.

=LEN(A4)-MATCH(TRUE,MID(A4,LEN(A4)-ROW(INDIRECT("1:"&LEN(A4)))+1,1)=".",0)+1

 

[Chris Leonard]

Excellent, thanks very much.

A neat solution.