I have a column that has dates, days, and times in it. I need to split out just the time but there are multiple hyphens, how can I achieve this? Below is what I have.

This is what I want

| Monday July 11 2022 | 8:00am - 10:00am |

CodePudding user response：

I would also try:

=left(A1,find("|",A1,1)-2)

And

=mid(A1,find("|",A1,1) 2,len(A1))

Which will split either side of the |.

Check for the formatting for your results.

CodePudding user response：

If the columns are going to be consistent, I would use the "Fixed width" option instead of "Delimited". You can tell it exactly where you want the field to be split.

CodePudding user response：

Another solution is to temporarily avoid the second hyphen with some find and replace actions:

1. find and replace in your relevant column of times/dates: m - replace with m
2. Text-to-columns split on the first hyphen
3. find and replace in the times column: m replace with m -

The use of the character is simply a convenient choice here.

CodePudding user response：

You can do this by using the formula

=right(a1,19)

This will extract the last 19 characters of the cell in a1.