0

I have the following text

Stack.over.flow 
sweet.stand.pro.flow
stop.fly.keep.flow
tank.staff.snack.flow 
stop.flow.over.flow

I want a Formula in excel giving the following output:

Stack.over.flow -------> over.flow
sweet.stand.pro.flow --> pro.flow
stop.fly.keep.flow ----> keep.flow 
tank.staff.snack.flow -> snack.flow
stop.flow.over.flow ---> over.flow

How can I achieve that please?

Fernando Barbosa
  • 853
  • 1
  • 8
  • 24
MindC3ll
  • 45
  • 7
  • Does this help? https://trumpexcel.com/find-characters-last-position/ The only difference is that they are trying to find the last slash instead of the last dot. – Jerry Jeremiah Aug 05 '21 at 21:41
  • And this may help as well - https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string – Jerry Jeremiah Aug 05 '21 at 21:47
  • @JerryJeremiah Thanks a lot, this formula find and delete last position of a forward slash and extract all the text to the right of it. What I want is the to skip the last dot or slash or whatever and jump to the next one and delete everything to the left. – MindC3ll Aug 05 '21 at 21:49

3 Answers3

2

Use this which finds the second to last . and then returns all the string after that:

=IFERROR(MID(A1,FIND("{{{",SUBSTITUTE(A1,".","{{{",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,LEN(A1)),A1)

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

B1

=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",66),LEN(A1)-LEN(SUBSTITUTE(A1,".",))-1),66))

B1 Array formula

=VLOOKUP(".*.*",RIGHT(A1,ROW($1:$23)+{1,0}),2,)
Can.U
  • 461
  • 2
  • 8
0

another alternative solution using FILTERXML (not available for non-Windows computers): =TEXTJOIN(".",0,FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[last()-1]"),FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[last()]"))

P.b
  • 8,293
  • 2
  • 10
  • 25