CODEWITHSUNDEEP
excelformula
Kevin Potatoo
Kevin Potatoo

Reputation: 55

How can I remove the all the characters after second "." from the right in excel?

How can I remove all the characters after the second "." from the right in excel?

E.g I have a file called dr_kk's_office_on_level_12_omb.msg.1370398310562.ver1 and I want to remove '.1370398310562.ver1' .. meaning second full stop "." from the right including the full stop.

May I know how I should go about this? Thanks

Upvotes: 0

Views: 923

Answers (2)

Michal
Michal

Reputation: 5750

==LEFT(A5,FIND("~",SUBSTITUTE(A5,".","~",LEN(A5)-LEN(SUBSTITUTE(A5,".",""))-1))-1)

enter image description here

Upvotes: 2

Jim
Jim

Reputation: 44

=LEFT(A3,FIND(".",A3,FIND(".",A3)+1)-1)
  1. Use "FIND"function as below:
FIND(".",A3)

to get the position of first "."

  1. Use "FIND" function, as below:
FIND(".",A3,FIND(".",A3)+1)

to get the position of second "."

  1. Use Left Function, with the position of Second "."
LEFT(A3,FIND(".",A3,FIND(".",A3)+1)-1)

to remove the string after Second ".", -1 is to include the second "." in the process of removing.

Upvotes: 1

Related Questions