# Converting imported data to hours|minutes AM/PM

#### sony_g

##### New Member
I have 3000 lines of data that was imported from another database and appears as follows:

 7:23PM 12:34PM 7:38PM 10:39PM 7:23PM 12:45PM

<tbody>
</tbody>

I need this to be hour: minute am/pm. Formatting does not work.

I assume it needs to be one of those “text” formulas. Can someone suggest how to write this or perhaps some other type of formula.

Cheers!

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Welcome to the forum.

is it always 2 characters at the end? AM/PM or are some just A or P? Are there N for Noon and M for midnight?

If it is always 2 characters AM/PM then then you can use this simple formula in an adjacent column:

=TIMEVALUE(LEFT(A1,LEN(A1)-2) & " " & RIGHT(A1,2))

It Works!!!!

Very Appreciated!!!!!

thanks for the feedback.

I've dealt with this issue a number of times and have a much more complicated formula if they include things like N and M or don't have the minutes like 1A for 1:00 AM

If you want to share that I will definitely be interested because I work with data that is imported from many sources and it is often #@&?(ed up!!!!

 1:30:00 PM 1:30pm 1:30am 130p 1p 130a 1a 12pm 1210p 12a

<tbody>
</tbody>

Just remember, you asked for it.

Here is the formula:

=IF(ISNUMBER(A1),A1,IF(ISERROR(SEARCH(":",A1)),IF(ISERROR(SEARCH("A",A1)),IF(ISERROR(SEARCH("P",A1)),"err",IF(SEARCH("P",A1)<4,TIMEVALUE(LEFT(A1,SEARCH("P",A1)-1)&":00 PM"),IF(SEARCH("P",A1)=4,TIMEVALUE(MID(A1,1,1)&":"&MID(A1,2,2)&" PM"),TIMEVALUE(MID(A1,1,2)&":"&MID(A1,3,2)&" PM")))),IF(SEARCH("A",A1)<4,TIMEVALUE(LEFT(A1,SEARCH("A",A1)-1)&":00 AM"),IF(SEARCH("A",A1)=4,TIMEVALUE(MID(A1,1,1)&":"&MID(A1,2,2)&" AM"),TIMEVALUE(MID(A1,1,2)&":"&MID(A1,3,2)&" AM")))),IF(ISERROR(SEARCH("A",A1)),IF(ISERROR(SEARCH("P",A1)),"err",TIMEVALUE(LEFT(A1,SEARCH("P",A1)-1)&" PM")),TIMEVALUE(LEFT(A1,SEARCH("A",A1)-1)&" AM"))))

I think this could be done in a simpler way. I did this a number of years ago and have learned more tricks since then but this seems to work.

Oh Dear God ...shoot me now !!! I got lost in the formula and could not find my way out! ...I will just keep using the simple one....

Thanks again.

Replies
3
Views
426
Replies
3
Views
2K
Replies
5
Views
348
Replies
4
Views
239
Replies
1
Views
199

1,196,329
Messages
6,014,678
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back