Excel Formula Help

[dasdsdsaadsdsadasdasadas]

I want to do this with only formula, not any scripts.

I want to be able to Remove everything BEFORE the LAST occurrence of the a period (and keep the cell blank if there isn't a period present).

Ex: This table shows what I need it to do.My current formula handles rows 2-4. However, I need help with row 5.[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD]Note[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]helloworld.pdf[/TD]
[TD]pdf[/TD]
[TD]removed everything before last period[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]hi.hop.doc[/TD]
[TD]doc[/TD]
[TD]notice this has two periods. it needs to remove everything BEFORE the last period[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]happytimes\document.txt[/TD]
[TD]txt[/TD]
[TD]removed everything before last period[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]document1[/TD]
[TD][/TD]
[TD]Since a period is not present. I need it to be blank.[/TD]
[/TR]
</tbody>[/TABLE]
The formula below works great at removing everything after last occurrence of the period. However, it returns the cell value if a period isn't present in column A (as shown below).
=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2)),LEN(A2)))

Ex:
[TABLE="width: 500"]
<tbody>[TR]
[TD]5[/TD]
[TD]document1[/TD]
[TD]document1[/TD]
[TD]Since a period is not present. I need it to be blank. The formula is currently returning the value in column A when a period isn't present.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try

=IF(ISNUMBER(SEARCH(".",A2)),TRIM(LEFT(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2)),LEN(A2))),"")

M.

 

[Jonmo1]

Perhaps

=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A2,".",REPT(" ",LEN(A2))),LEN(A2)),A2,""))

 

[dasdsdsaadsdsadasdasadas]

Both work. Thanks!

 

[ArthriticPanda]

I see you have already been provided with an answer, but here is another one which may work:

=IF(ISERR(FIND(".",A1,1)),"",RIGHT(A1,LEN(A1)-FIND(".",A1,1)))

 

[Jonmo1]

=IF(ISERR(FIND(".",A1,1)),"",RIGHT(A1,LEN(A1)-FIND(".",A1,1)))

Works well except on strings with more than 1 period (hi.hop.doc returns as hop.doc)

 

[Jonmo1]

Both work. Thanks!

You're welcome.

 

[ArthriticPanda]

Works well except on strings with more than 1 period (hi.hop.doc returns as hop.doc)

Good point. Thanks Jonmo1.

 

[ArthriticPanda]

Just for the hell of it i had another go and came up with this which seems to work with double '.':

=IF(ISERR(FIND(".",A1,1)),"",RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))))

AP