Reputation: 45
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
Reputation: 3802
Another option,
In B1
, enter formula :
=TRIM(RIGHT(SUBSTITUTE(A1,"#",REPT(" ",99)),99))
and format the result to your desired Date format
Upvotes: 1
Reputation: 27243
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)),"")
Upvotes: 2