CODEWITHSUNDEEP
excel
Brooke Taylor
Brooke Taylor

Reputation: 141

How can you split text to columns if there are multiple delimiters of the same type in Excel?

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.

Recording Time
Monday July 11 2022 - 8:00am - 10:00am

This is what I want

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

Upvotes: 0

Views: 4410

Answers (3)

Billpo
Billpo

Reputation: 3

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, specify the formatting for each new column, etc.

Upvotes: -1

Solar Mike
Solar Mike

Reputation: 8375

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 first -.

Check for the formatting for your results.

Upvotes: 3

topsail
topsail

Reputation: 3119

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.

Upvotes: 1

Related Questions