data cleanup

  Could you show me a formula for separating letters off text that does not have spacing for exapmle i have time format that 7:45PM there is no spacing between the 5 and PM how do i separate as will have 2 date formats PM & AM


Selected Answer

The time you see displayed should be the rendering of a decimal value in the cell, a number with which you can do calculations. One day is represented by the integer 1. One hour therefore = 1/24. 7:45 = 1/24*7.75. If it's PM add 0.5.

The display is controlled by the cell's number format. A display like 7:45PM would have a Custom number format of h:mmAM/PM. The way to add a space would be to add it in the mask: h:mm AM/PM. (Right-click > Format Cells > Number > Custom)

However, your data may have been converted to text before you received it. In that case the value of the cell really is '7:45PM (with a leading apostrophe unless the cell is actually formatted as Text). In that case you have two options. One is to just insert the space. (In my examples below B8 contains the text.)

=LEFT(B8,LEN(B8)-2) & " " & RIGHT(B8,2)

The other way - probably the better one - would be to convert the text to a proper time value and then format the cell whichever way you want. The formula below will convert the text in B8.

=TIMEVALUE(LEFT(B8,LEN(B8)-2)) + IF(RIGHT(B8,2)="PM",0.5,0)


Thank you soo much, this has really sorted my data issues - the frst fromula was perfect just saved me 2 hours of data sorting 

Sam Lee (rep: 2) Dec 15, '18 at 4:37 pm
That’s great news, Sam. Thanks for letting me know!
Variatus (rep: 2354) Dec 15, '18 at 7:51 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login