How to retrieve text from another cell AFTER a certain character

Hi guys,

Can anyone help me with a formula to retrieve text from Cell A3 which will always have text starting with DD/MM - Name: Info text is here

I want a formula I.E. using the RIGHT function which always retrieves text (length of text will always vary) AFTER the ':'.

Can anyone advise?

Thank you!

Assuming that there is only one instance of ':' in each text string:

=TRIM(RIGHT(A1,LEN(A1)-FIND(":",A1)))

Which reminds me that you could use the text to columns feature on the data ribbon, setting ':' as the delimiter.

Same sort of formula as Ali's:

=TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",100)),100))

Hi AliGW,

thanks for the response. I am getting a #VALUE! error message...

just to re-iterate the ':' DOES NOT have a space in front of it, i.e. 10/11 - Name:

Hi Glenn,

This returns a blank cell...?

Doesn't matter - it will work regardless. Something else must be at play. You will need, therefore, to provide a sample workbook.

Excel 2016 (Windows) 32 bit

	B	C
4	skdhfoihsdfj:kahsfkh	kahsfkh
5	kashdkhsd : lhslfh	lhslfh

Sheet: Sheet2

But after : it has space
In PQ you can define: :_ (colon space) as delimiter

It doesn't matter whether or not it has spaces in my formula, so that's not the issue. Nor does it matter in Glenn's.

Typo!!!

=TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",50)),50))

Perfect Glenn - this does the job for me!

You're welcome... we're all allowed the occasional tpyo....

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Thanks for the rep!

Very curious why my suggestion did not work, though, because it should have if what you told us in the opening post was a true reflection of your data. Very perplexed.