CODEWITHSUNDEEP
excelparsingdatetimeformat
melonsvk
melonsvk

Reputation: 13

Parsing string in Excel - date and time

I have following problem:

I want to extract time interval from string like

time=10PM-12AM!991408111200000

The problem is, I need it in 24hrs format, so 10PM-12AM should be 22 and 00 in two different cells. I tried several solutions, but nothing worked for all possible intervals. Do you have any idea how can I do that? Thanks a lot!

Upvotes: 0

Views: 141

Answers (1)

ZeroX
ZeroX

Reputation: 23

This should work. Assume that your original string is on A1. The first code is for the first portion of the time. The second is for the time after the dash.

=TEXT(IFERROR(REPLACE(MID(A1,FIND("=",A1)+1,FIND("-",A1)-1-FIND("=",A1)),FIND("PM",MID(A1,FIND("=",A1)+1,FIND("-",A1)-1-FIND("=",A1))),2," PM"),REPLACE(MID(A1,FIND("=",A1)+1,FIND("-",A1)-1-FIND("=",A1)),FIND("AM",MID(A1,FIND("=",A1)+1,FIND("-",A1)-1-FIND("=",A1))),2," AM")),"HH:MM")

=TEXT(IFERROR(REPLACE(MID(A1,FIND("-",A1)+1,FIND("M",A1,FIND("-",A1))-FIND("-",A1)),FIND("PM",MID(A1,FIND("-",A1)+1,FIND("M",A1,FIND("-",A1))-FIND("-",A1))),2," PM"),REPLACE(MID(A1,FIND("-",A1)+1,FIND("M",A1,FIND("-",A1))-FIND("-",A1)),FIND("AM",MID(A1,FIND("-",A1)+1,FIND("M",A1,FIND("-",A1))-FIND("-",A1))),2," AM")),"HH:MM")

The reason this is so long is because I am not sure if your data always comes up with four characters for the time. For example, if your data has time=3PM-6PM this should be smart enough to understand that there is less spaces in between. This also assumes that the exclamation mark just happened to be randomly there rather than always there.

Let me know if you have a problem.

Upvotes: 1

Related Questions