Help with formulas and macros

  steviegee 09:40 23 Feb 10

I have a spreadsheet which needs certain formulas and macros. I have info from columns 'n' onwards which need formulas to take certain info from each cell in 'a' to 'm'.

a2 needs to take all info from y2
b2 needs to take all info from o2
c2 needs to take first to characters from o2
d2 needs to take all info from u2
e2 blank
f2 needs to take characters 5-10 inc from k2
g2 needs to take first 4 characters from k2
h2 needs to look at the last character in y2 and if it is an 'a' put in the word petrol and if it is a 'd' put in the word diesel
i2 need to look at w2 and take out the first 6 characters
j2 needs to be the result of h2
k2 needs to take all info from v2
l2 blank
m2 needs to take all info from z2

I know this is difficult and looks complicated but this will save me a lot of time and get me big browny points at work!

Once I have done row 2 I can fire it down the rest of the spreadsheet with the black cross. It could be as many as 6000 rows!!!

Thanks very much.

  VoG II 09:51 23 Feb 10

A2 =Y2
B2 =O2
C2 =LEFT(O2,2)
D2 =U2
F2 =MID(K2,5,6)
G2 =9LEFT(K2,4)
H2 =IF(RIGHT(Y2,1)="a","petrol","diesel")
I2 =RIGHT(W2,LEN(W2)-6)
J2 =H2
K2 =V2
M2 =Z2

For I2 I assumed that you wanted to remove the first 6 characters from W2.

  MAJ 10:03 23 Feb 10

G2 =9LEFT(K2,4)

  steviegee 10:03 23 Feb 10

Thanks VoG just one small change

j2 if h2 is petrol to leave it blank.

  steviegee 10:03 23 Feb 10

It was a typo I took the 9 out.

  VoG II 10:08 23 Feb 10

Fat fingers!

J2 =IF(H2="petrol","",H2)

  steviegee 10:13 23 Feb 10

Also a couple of small problems. When doing f2 the cell k2 contains a date and the formula doesn't recognise the first 0 e.g k2 is 1.8 03/94-12/97 and the result in f2 is 3/94-1 instead of 03/94-

And also when doing i2, w2 doesnt always have anything in it so in i2 i get a return of #value! when I need it to be blank.

  VoG II 10:21 23 Feb 10

If F2 is actually a date, not a 'text date'

F2 =MID(TEXT(K2,"dd/mm/yy"),5,6)

I2 =IF(W2="","",RIGHT(W2,LEN(W2)-6))

  VoG II 10:28 23 Feb 10


1.8 03/94-12/97

in K2


gives me 03/94-

  steviegee 11:46 23 Feb 10

Almost there! On the f2 one it may need to take characters 6-11 from k2 as there may be 5 characters in front of it as well as 4 e.g 2.0d 01/01-12/02 rather than 1.8 01/01-12/02.

  VoG II 11:57 23 Feb 10


=MID(K2,FIND(" ",K2)+1,6)

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

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

The updated 'Corel Painter inside Photoshop' plugin ParticleShop offers new brushes

New MacBook Pro 2017 release date, UK price and tech specs: Kaby Lake MacBook Pro to launch in…