Help with formulas and macros

  steviegee 09:40 23 Feb 2010

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 2010

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 2010

G2 =9LEFT(K2,4)

  steviegee 10:03 23 Feb 2010

Thanks VoG just one small change

j2 if h2 is petrol to leave it blank.

  steviegee 10:03 23 Feb 2010

It was a typo I took the 9 out.

  VoG II 10:08 23 Feb 2010

Fat fingers!

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

  steviegee 10:13 23 Feb 2010

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 2010

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 2010


1.8 03/94-12/97

in K2


gives me 03/94-

  steviegee 11:46 23 Feb 2010

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 2010


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

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

Elsewhere on IDG sites

Huawei MateBook X Pro review

8 digital brands that designed custom typefaces to save millions

How to speed up a slow Mac

Comment résoudre des problèmes d’impressions ?