Get the string after the last character in excel

Question

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

if there is only one then use find() with right() or mid() and if there are two then use find() inside find() — Solar Mike, Mar 05 '22 at 21:27
Then as I suggested use find() inside find()... find("#",A1,find("#",A1,1)+1) — Solar Mike, Mar 05 '22 at 21:37
If you have Windows environment you may look into `FILTERXML`. https://stackoverflow.com/a/61837697/12634230 — P.b, Mar 05 '22 at 21:58
Sure. Give credits to JvdV in your answer. Note it only works on Windows environment. — P.b, Mar 05 '22 at 22:09

Mayukh Bhattacharya · Answer 1 · 2022-03-05T22:20:11.040

2

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

edited Mar 05 '22 at 22:20

answered Mar 05 '22 at 21:45

1

That was the whole idea, you didn't need to credit me for this, you are an excel-formula master, not me, I rarely look into it. – VBasic2008 Mar 05 '22 at 22:23
1

`FILTERXML()` is good choice for this situation. – Harun24hr Mar 06 '22 at 06:52

bosco_yip · Answer 2 · 2022-03-06T05:39:25.317

1

Another option,

In B1, enter formula :

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

and format the result to your desired Date format

edited Mar 06 '22 at 05:39

answered Mar 05 '22 at 23:57

bosco_yip