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.

	A	B	Note
2	helloworld.pdf	pdf	removed everything before last period
3	hi.hop.doc	doc	notice this has two periods. it needs to remove everything BEFORE the last period
4	happytimes\document.txt	txt	removed everything before last period
5	document1		Since a period is not present. I need it to be blank.

<tbody>
</tbody>

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:

5

document1

document1

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.

<tbody>
</tbody>

 

[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