Formula Please

  steviegee 08:16 14 Jan 11
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 11

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 11

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 11

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 11

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 11

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 11

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 11

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 11

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.

Hands-on: Samsung Galaxy S8 review

1995-2015: How technology has changed the world in 20 years

Samsung's beautifully designed Galaxy S8 makes for better VR experiences too

47 iPhone camera tips to help you take better photos