Remove partial text from cell

**shawnoot** · 07-13-2014, 10:58 PM

I am copying text and posting into excel. As it is formatted on the original site, it posts the date, followed by time.
example: 26/5/14 20:16 (Please refer to screen shot. The text in the cell is shown as 26/5/14 20:16, the actual value is 26/5/2014 8:16:20 pm).

I need to have the dates removed.
Example: 21:18

I've found some similar cases in other thread and the answer seems to be something like this =SUBSTITUTE(LEFT(A6,SEARCH("-",A6)-1),".","")
I can't seems to get it work. Im not sure where have I gone wrong. I would greatly appreciate advice from you.

Or should I be looking for a different method?

(It is not an option to have the original web site change their posted format).

**shawnoot** · 07-13-2014, 11:29 PM

Bumped into someone who knows excel and got my answer. Thanks!

**hemesh** · 07-13-2014, 11:33 PM

try below
=SUBSTITUTE(TEXT(A3,"dd/MM/YYYYY HH:MM am/pm"),TEXT(A3,"dd/MM/YYYYY "),"")

change the references accordingly

if you have text entries then use
=RIGHT(A3,LEN(A3)-FIND(" ",A3))