MindC3ll
MindC3ll

Reputation: 45

Get the string after the last character in excel

I have the following string in excel cell

"myshop.com" "great"#12#20 Feb 2022

How I can get the string after the last Hash

Output

20 Feb 2022

Upvotes: 0

Views: 1443

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Another option,

In B1, enter formula :

=TRIM(RIGHT(SUBSTITUTE(A1,"#",REPT(" ",99)),99))

and format the result to your desired Date format

enter image description here

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

Edit

To get the string after the last character Hash in excel, you may try anyone of the following solutions,

Solution as suggested by P.b --> Credit goes to JvdV Sir

Formula used in cell C1

=FILTERXML("<a><b>"&SUBSTITUTE($A1,"#","</b><b>")&"</b></a>","//b[last()]")

Solution suggested by VBasic2008 Sir,

Formula used in cell D1

=REPLACE(A1,1,FIND("@",SUBSTITUTE(A1,"#","@",LEN(A1)-LEN(SUBSTITUTE(A1,"#","")))),"")

Solution I have posted before edit

Formula used in cell B1

=--REPLACE(A1,1,FIND("@",SUBSTITUTE(A1,"#","@",2)),"")

ALTERNATIVE_SOLUTIONS

Upvotes: 2

Related Questions