Formula Please

  steviegee 08:16 14 Jan 2011
Locked

In column AB (starting row 2) I have data in the following format :- 1.8 03/94-12/97 or 2.2D 01/10- and so on all the way down. What I need to do is split them all up in the following way (text to columns won't do it properly)

1.8 in column L
03 in column N
1994 in column O
12 in column P
1997 in column Q

Also if the first data has nothing after it (1.8)I need it to put the word Petrol in column S but if it has a D after it (2.2D) I need it to put the word Diesel in column S.

Then I can fire it down the 20000 rows I may have! Thanks.

  VoG II 09:00 14 Jan 2011

Text to Columns will work but you need to do it in 2 stages. First use Space and Other = /. Then Other = -.

  steviegee 09:08 14 Jan 2011

I tried most of yesterday and it just didn't work and got me so frustrated. Is there no formula then? I think you done something similar for me in the past.

  VoG II 09:26 14 Jan 2011

For 1.8 =LEFT(AB2,FIND(" ",AB2)-1)

03 =MID(AB2,FIND(" ",AB2)+1,2)

94 =MID(AB2,FIND("/",AB2)+1,2)

12 =MID(AB2,FIND("-",AB2)+1,2)

97 =RIGHT(AB2,2)

  steviegee 09:36 14 Jan 2011

Thanks works good just a couple of things. I need the years as 1994 not just 94 etc and also what about the final part where i need it to put petrol/diesel in for me. Thanks.

  VoG II 09:41 14 Jan 2011

1994 =MID(AB2,FIND("/",AB2)+1,2)+1900

1997 =RIGHT(AB2,2)+1900

Petrol/Diesel = IF(RIGHT(L2,1)="D","Diesel","Petrol")

  steviegee 09:45 14 Jan 2011

The petrol/diesel one worked fine. Sorry to be a pain but there may be years that are in the 'noughties' e.g 1.6 01/04-02/08 as well.

  VoG II 09:52 14 Jan 2011

First year =IF(MID(AB2,FIND("/",AB2)+1,1)="0",MID(AB2,FIND("/",AB2)+1,2)+2000,MID(AB2,FIND("/",AB2)+1,2)+1900)

Second year =IF(MID(AB2,LEN(AB2)-1,1)="0",RIGHT(AB2,2)+2000,RIGHT(AB2,2)+1900)

  steviegee 10:04 14 Jan 2011

Thanks VoG you are a genius! I won't close the thread yet as I may need some more help on the same spreadsheet!

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

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?