CODEWITHSUNDEEP
excelexcel-formula
MindC3ll
MindC3ll

Reputation: 45

Delete everything in a string before the first period in excel

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?

Upvotes: 0

Views: 685

Answers (3)

P.b
P.b

Reputation: 11425

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()]"))

Upvotes: 0

Can.U
Can.U

Reputation: 481

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,)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

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

Upvotes: 2

Related Questions