Custom Excel function

[Hrvoje19]

Does anyone know how to make an excel function which changes all numbers in one column so that number xxyyxxxx becomes number xx(yy-5)xxxx ??? thanks!

(it is for making everyone 5 years older )

[JaoSming]

um, -50000, lol
you just need to subtract it like that

[Hrvoje19]

I figured it out!

lets say in 'A' column are original birthdates from players.dbf

so the function is:

=LEFT(A1;2)&MID(A1;3;2)-5&RIGHT(A1;4)

etc. one player has value 19750508, with function you get him 5 years older - 19700508

[JaoSming]

...or you could have just done =A1-50000

Code: Select all

19750508
  -50000
19700508

but whatever works for you, at least it works

[Hrvoje19]

...or you could have just done =A1-50000

Code: Select all

19750508
  -50000
19700508

but whatever works for you, at least it works

lol, I thought you were just joking!

anyway, thanks. but I'm not certain how to paste that into function and apply to the whole column in excel

[JaoSming]

well what I do is copy out the entire column to a new spreadsheet

paste it in then a few cells away, in the same row though, type = then click the top birthdate then add the -50000

once that is in, select the cell with the updated birthdate, youll see a little black square in the bottom right of the cell

click, hold, and drag that down till you edit all of the values

then copy it back into the dbf

[Hrvoje19]

Now, I have cells with functions, how can I copy values to a different file?? Because when i go 'copy' and 'paste' it copies functions instead values!

EDIT: paste special - values does the trick