How to Remove dates from a cell?

[sn152]

Hi All,

Pls let me know how to remove dates from each cell which also has text in it.
For example: Daily production report - 1/20/2015 10:34:43 AM. Like this each cell in the column will have text and date. I have to remove the date and time alone. Pls help me.

Thanks!

 

[Nebu]

Hi:

if there will be a hyphen in between the string in all your texts you can use the following formula to separate time and date from the text
=TRIM(RIGHT(A1,LEN(A1)-SEARCH("-",A1,1)))

Thanks

 

[Khalid NGO]

Hi,
Or you can use this simple one if length is same:

=RIGHT(A1,21)

 

[prasaddn]

Hi,

Here is one more alternate:
1. Use Find and Replace window, by calling Ctrl+H
2. Type "Daily production report *" in Find what field
3. Type "Daily production report" in Replace with field
4. Click Replace All button.

5. Say.. Wohoo!! it is done,

Regards,
Prasad DN

 

[prasaddn]

Hi,
Or you can use this simple one if length is same:

=RIGHT(A1,21)

The OP wants to remove d&t, I think its just a mere confusion, it should be left(A1,23).

 

[Khalid NGO]

The OP wants to remove d&t, I think its just a mere confusion, it should be left(A1,23).

Hi Prasad

Since OP requirement is not much clear, I assumed he may in need of actual date / time:

for Date:
=DATE(MID(A1,FIND(":",A1)-7,4)+0,MID(A1,26,2)+0,MID(A1,FIND("/",A1)+1,2)+0)

for Time:
=TIMEVALUE(RIGHT(A1,12))

Regards,