what function in Excel returns say the 3rd to the 5th characters in a cell?

tell-me-more | 14:07 Wed 05th Apr 2006 | Technology

4 Answers

e.g. if I have a cell saying 01-JAN-2006, how do I pick out the 01 and the JAN to use in other formulae?

Many thanks in anticipation

whats wrong with my dvd+r rw drive?

AV Senders

Answers

1 to 4 of 4

Best Answer

No best answer has yet been selected by tell-me-more. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.

Hammer

You need the MID function.

=MID(A1,4,3) looks at cell A1 and gives 3 characters starting from the 4th character.

kempie

... but MID by itself will use the date code instead of the actual date so you need to interpret the date as text

=MID(TEXT(A1,"dd-mmm"),1,2) returns the day

=MID(TEXT(A1,"dd-mmm"),4,3) returns the month

PowderKeg

If Cell A1="01-JAN-2006"

day(A1) = 1

Month(A1) = 1

Year(A1) = 2006

If Cell A2= Month(A1), formatting this cell as "mmm" will display JAN

tell-me-more

Question Author

Brilliant answers. Thank you to each of you. I actually used the day, month functions in the end, but each answer was useful to me for future reference.

1 to 4 of 4

Do you know the answer?