Excel 2007 : is a mass convert of this possible?

[midian777]

I have a unique issue. I have a tool that we use to export to excel. The tool displays this format and excel will not convert it to any time format that it recognizes.

when exported it shows 8:37am (the am being attached is the problem, but they cant change the way it exports)

Is there an easy way to make all the time entries have a space in them so it would show this instead 8:37 am?

any help would be appricated.

[DonkeyOte]

Run an edit replace - replacing "am" with "_am" (where _ is space) and repeat for "pm" - this should coerce the values to time.

(p.s it's not really a unique problem...)

[midian777]

true enough I correct6 that with unique for me lol.

[midian777]

sorry im still new in general to excel how do you run the edit replace (control f?)

[royUK]

In your spreadsheet, select any cell. The press Ctrl + F to display the Find & Replace dialog.

[midian777]

I tried the control f and searching out AM, it says that the excel could not find the data I was looking for

[DonkeyOte]

presumably you're running a partial search ?

If you are and are still experiencing problems post a sample data set.

[midian777]

Okay I need these times to display in military time, thats the end goal I am after.

PS thanks for all the help in advance everyone.

[DonkeyOte]

I'm afraid the Find & Replace worked for me... to outline the steps

1.
ALT + E -> E
(to bring up Find & Replace dialog)

2.
Find what: am (or AM)
Replace with: [space]am

3.
Click Replace All

Repeat steps 2 & 3 using pm (or PM)

At this point all the values in A are now time values (header excepted) and as such can be formatted as Time using whichever time format you wish.