Help with Excel

Forums › IT Pro and developers › Help with Excel

Byrned

455 posts

Ultimate Geek
+1 received by user: 27

#205211 3-Nov-2016 10:45

For the life of me I cannot work out how to do this.

I have an excel sheet that is about 6000 lines long, but that dates are formatted as "2 weeks 2 days" and "1 month 1 week".

Is there any easy way to convert them into something that I can then sort on - days would be ideal! As it is now, 1 month.. comes before 2 weeks!

evnafets

567 posts

Ultimate Geek
+1 received by user: 269

Trusted

Lifetime subscriber

#1663194 3-Nov-2016 11:01

I presume the dates of "1 month" and "2 weeks 2 days" are just text? i.e. somebody has just typed into the field?

Converting free text into a number can be difficult just because of all the variations and permutations that people can use.

How I would approach it:

Write a formula to split this one "date" column into multiple values: months, weeks, days. If it is in multiple columns, it becomes easier to work with.

i.e. 1 month would be 1 0 0. 2 weeks 3 days would be 0 2 3

You could then work out an approximate length of months * 30 + weeks * 7 + days to sort on.

You might require 5-6 columns of interim calculations to get there.

Of course I'm a computer programmer rather than an Excel expert, so there might be a much easier way...

KrazyKid

1247 posts

Uber Geek
+1 received by user: 616

#1663201 3-Nov-2016 11:08

If you need to split the columns and the are text these 2 formulas will let you pull out a set number of characters (ie "1 month"):
Left(cell address, number of Chars) - eg Left(A2,7) will get you the leftmost 7 characters from Cell A2

Right(cell address, number of Chars)

These can be nested and combined with =IF as required