Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

data cleanup

0
  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
Answer
Discuss

Answers

0
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)
Discuss

Discussion

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


Cheers 
Sam Lee (rep: 2) Dec 15, '18 at 4:37 pm
That’s great news, Sam. Thanks for letting me know!
Variatus (rep: 4889) 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