convert text value to time value

[vogel997]

I'm looking for a formula to convert a text value into a time value in a excel data base
text value "0930a" --> need to convert to "9:30 am" as a time value.

Thank you for any advise.

 

[JoeMo]

Format column B as time and copy B1 down.

Excel Workbook
	A	B
1	0930a	9:30 AM
2	0235p	2:35 PM
Sheet3

 

[vogel997]

Thank you, that works!

 

[Rick Rothstein]

Here is another formula for you to consider...

=0+REPLACE(REPLACE(A1,5,0," "),3,0,":")

Put this formula in cell B1, format the cell as Time and then copy down as needed.

 

[JoeMo]

Thank you, that works!

You are welcome - thanks for the reply.

 

[vogel997]

Works well, Thank you!