Remove text characters from Dates in Excel

Hello Experts,

Is there a way to strip text from a date. I know how to use the LEFT and RIGHT functions to remove characters from strings but what about for dates. When I use the LEFT function on the date below to remove the "a",

1/13/15 15:04 a

it becomes a text format that won't change back to date format. When I manually delete the "a", it works fine and stays as a date but I have thousands of these and can't manually do it. Is there a way to strip those "a"s?
LVL 5
Tj aAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Or

=VALUE(LEFT(A1,LEN(A1)-2))
0
 
gowflowCommented:
Try this by formula where your 1/13/15 15:04 a is in Cell A1
=DATEVALUE(LEFT(A1,LEN(A1)-2))

Now if you have several dates as you mentioned you mean a VBA solution ?
if yes pls post the workbook

gowflow
0
 
Ejgil HedegaardCommented:
Try this
=DATEVALUE(SUBSTITUTE(A2," a",""))+TIMEVALUE(SUBSTITUTE(A2," a",""))
And format the cell to show date and time.
0
 
Tj aAuthor Commented:
Great. All the solutions worked. The only thing though about gowflow's solution is that it changes all the timestamps to 12:00. Thank you all.
0
 
gowflowCommented:
Correct !!!
nice shot Saqib !

gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.