Excel Question

  [email protected]© 14:21 14 Apr 2008

I have a column consisting of 1000 rows. In each cell it is in a format of 3.0 4/91-12/91. I want it to read whenever there is a single digit in the date to put a zero in front of it i.e 3.0 04/91-12/91 or 4/91-8/91 to read 04/91-08/91. Is there an easy way of doing this?

  kdt 15:31 14 Apr 2008

wait for Vog to reply

  VoG II 15:57 14 Apr 2008

Given a column of text like

3.0 4/91-12/91

Make sure that you have at least 3 blank columns to the right.

Select your column of text (I am assuming column A), Data > Text to columns, tick Delimited, specify Space as the delimiter and click Finish. This will split the data to give you (in the first row)

3 4/91-12/91

Now select column B, Data > Text to columns, tick Delimited, specify Other and enter - in the box. This will give you (in the first row)

3 Apr-91 Dec-91

Now in D1 enter the formula:

=TEXT(A1,"0.0")&" "&TEXT(B1,"mm/yy")&"-"&TEXT(C1,"mm/yy")

Click in D1, hover the cursor over the bottom right of the cell until the cursor turns into a + and double click to copy the formula all the way down to the end of your data.

Select the whole of column D and copy it. With column D selected, Edit > Paste Specials, tick Values and click OK.

Delete columns A to C.

  [email protected]© 09:44 15 Apr 2008

Thansk Vog, I forgot to say some don't have an end date i.e 1.6 4/98- and if I do the formula you say it puts in 01/00 as an end date when it should be blank.

  [email protected]© 10:15 15 Apr 2008

Also if the end date is past 2000 it changes round!! Instead of 08/03 it says 03/08!

  VoG II 11:04 15 Apr 2008

This should cover those with and without end dates

=IF(C1<>"",TEXT(A1,"0.0")&" "&TEXT(B1,"mm/yy")&"-"&TEXT(C1,"mm/yy"),TEXT(A1,"0.0")&" "&TEXT(B1,"mm/yy"))

I can't reporoduce the problem with dates after v2000 - they work for me.

  [email protected]© 11:26 15 Apr 2008

Thanks that works apart from the little dash after the number!!

  VoG II 20:50 15 Apr 2008

No :o)

With a code like 3.0 4/91-12/91 in A1

B1 =LEFT(A1,FIND(" ",A1)-1)

C1 =IF(ISNUMBER(FIND("-",A1)),MID(A1,FIND(" ",A1)+1,FIND("-",A1)-FIND(" ",A1)-1),RIGHT(A1,LEN(A1-FIND(" ",A1))))

D1 =IF(ISNUMBER(FIND("-",A1)),RIGHT(A1,LEN(A1)-FIND("-",A1)),"")

E1 =IF(LEN(C1)=4,"0"&C1,C1)

F1 =IF(LEN(D1)=4,"0"&D1,D1)

G1 =IF(D1="",B1&" "&E1,B1&" "&E1&"-" &F1)

Copy the formula down as far as needed. Column G now contains your reconstructed codes.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Huawei MateBook X Pro review: Hands-on

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?