Changing Data in a Column
 

I have a column that has time ranges in it. Example: Column G Row 3 thru
253 has the following time ranges: 11am-1pm, 1pm-3pm, 12pm-5pm, 8am-12pm

I need cells with 11am-1pm to read 13:00
I need cells with 1pm-3pm to read 15:00
I need cells with 12pm-5pm to read 17:00

Is there a formula that I can use the look up the entire column and make all
the changes at once, or do I have to use the find a replace.

The correct format can appear in a different column.

Please help

Changing Data in a Column
 

Try this formula...

=--SUBSTITUTE(SUBSTITUTE(MID(A6,FIND("-",A6)+1,11),"pm"," pm"),"am"," am")

and format the cells to display the cell contents using the 13:30 Time
format option.

--
Rick (MVP - Excel)


"DJordan" wrote in message
...
I have a column that has time ranges in it. Example: Column G Row 3 thru
253 has the following time ranges: 11am-1pm, 1pm-3pm, 12pm-5pm, 8am-12pm

I need cells with 11am-1pm to read 13:00
I need cells with 1pm-3pm to read 15:00
I need cells with 12pm-5pm to read 17:00

Is there a formula that I can use the look up the entire column and make
all
the changes at once, or do I have to use the find a replace.

The correct format can appear in a different column.

Please help