Need two times parsed from single cell and name parsed to first name last initial.

[GreggW]

I have an Excel 2013 file with two work sheets. One contains a single field for employee name and another a start and end time. I need to get the start and end times parsed out to single fields with correct formatting and need the employees full name parsed to just first name and last initial. Employee names in cells only have two variations, one is LAST, FIRST ex. "Smith, James" the other is LAST, FIRST MIDDLE ex. "Smith, James Michael". There is always a space after the comma and the first name is always directly to the right of that space. I need a way to get that onto a second worksheet formatted down to "James S."

The second field giving me a problem is the times. Our system formats them as four digit with AM/PM ex. " 09:00AM-02:15PM". For some reason the software outputs it with a leading space. I need a way to get the first time out into a single field formatted in a way that Excel recognizes it as a time and the same for the second field. The second time stamp always directly proceeds the dash.

Any help you guys can give in helping me finish this project is greatly appreciated!

The attached file has on the left the exact format as our scheduling system dumps it, lead spaces and all. On the right is the format my boss wants it in.

[azumi]

Can you post/upload your sample excel woorkbook at the forum? with manually type for the expected results? Click on "Go Advanced" button and find paperclip image button to attach it.........

[FlameRetired]

Try this name parsing formula in D6 and fill down.

Please Login or Register  to view this content.

Then in E6 this formula filled down and across to column F.

Please Login or Register  to view this content.

You will need to set the Excel formatting h:mm AM/PM.


Edited Found (another) mistake in name parsing formula.

[GreggW]

That's nearly perfect, time part is working exactly. The name is getting caught up on Middle names. So when a middle name is present it's doing MiddleName LastInitial instead of FirstName LastInitial.

[FlameRetired]

That's nearly perfect, time part is working exactly. The name is getting caught up on Middle names. So when a middle name is present it's doing MiddleName LastInitial instead of FirstName LastInitial.

Yes. I had misinterpreted. Edited my formula a bazillion times. I believe you'll find it works, now. Apologies.

[GreggW]

No need to apologize it's perfect. You have saved an enormous headache with my boss thank you!

[FlameRetired]

You're welcome. Glad it works....and thanks for the rep.