Excel help (select one field and put age in the next)

[sports_brah]

Is it possible to take the first column and to automatically get the age in the next column?

I'd do it by hand usually, but there is over 1,000 entries and I'm really busy!

Any help would be very much appreciated!

 

[sports_brah]

scan text for "aged " string and take next 2 characters.

http://office.microsoft.com/en-gb/excel-help/find-findb-functions-HP010342526.aspx

Assuming date of birth is on Cell A1, in Cell B2 put:

=(RIGHT(A2,3))

then in cell C3 put:

=LEFT(B2,LEN(B2)-1)

This will put the number you are after in Cell C3.

It's a bit long winded and I'm sure there is an easier way though.

I'd probably just go for something like

=MID(A1, LEN(A1)-2, 2)

In this case.
Assuming all ages you have are two digits anyway!

Both of these give the value 3) for the first one etc.

 

[sports_brah]

Yes, thats what the second part of mine does, strip the ) off.

I made a bit of a mess of the cell references though..

Assuming date of birth is on Cell A1, in Cell B1 put:

=(RIGHT(A1,3))

then in cell C1 put:

=LEFT(B1,LEN(B1)-1)

Try now

Same answer!

 

[sports_brah]

Just whack it all in one formula, assuming the format is always the same:

=LEFT(RIGHT(A2,3),2)

edit: of course, if you have anyone who isn't double digits years old this will fail! I'd not make a very good developer!

No luck with this one!

Have you got spaces at the end?

This one will trim the spaces
=MID(TRIM(A1), LEN(TRIM(A1))-2, 2)

We have a winner! Excellent stuff, thanks. That just saved me an hour!

 

[sports_brah]

Again, only works if you don't have anyone aged 9 or 109!

It's grand, they are all teenage upwards with no outliers of greater than 99.


All the data is in batches of 25 with a space underneath them. Going to calculate the average year, which will then be used as a dummy variable as a calculation of a result. Hopefully when I create the average, I'll just copy and past it for each batch and just change the cell references.

Last night will be long!



Thanks again for all your help everyone, some extremely clever guys here! Out of curiosity, did you learn your stuff through courses, or is most of it accumulated through time?