2009/9/26 Wade Smart <[email protected]>
> Harold Fuchs wrote:
>
>> 2009/9/26 Wade Smart <[email protected]>
>>
>> JOE Conner wrote:
>>>
>>> Wade Smart wrote:
>>>>
>>>> I have a group of numbers in a column
>>>>> 265879-09-24-71-918-333-0055
>>>>> and I need to take the fourth set of numbers, in this case the 71, and
>>>>> subtract it from the current year to get the age of the item.
>>>>>
>>>>> Ideas on how to do that?
>>>>>
>>>>> Wade
>>>>>
>>>>> Is this a spreadsheet column or is it writer?
>>>>
>>>> Sorry, spreed sheet.
>>>
>>>
>>> Wade
>>>
>>> Assume the numbers are in column A.
>>>
>>
>> 1. Copy column A to column B
>> 2. Select column B
>> 3. Go to the Find & Replace menu
>> 4. In the Find box enter
>> "[:digit:]+-[:digit:]+-[:digit:]+-([:digit:]+)-[:digit:].*" without the
>> quotes
>> 5. In the Replace box enter "$1" without the quotes
>> 6. Click More Options
>> 7. Check Regular expressions
>> 8. Check Current selection only
>> 9. Click Replace All
>> 10. The values in column B will change so that it only contains the 4th
>> number in each row.
>> 11. In column C enter an appropriate formula such as
>> "=YEAR(NOW())-(B1+1900)" assuming the "71" in your example means 1971.
>>
>>
>>
>>
> Hey, thats great.
> Ok. Anyway I can do this without having to add in this extra column each
> time I do the sheet?
>
>
> Wade
>
>
> Assuming the "71" part of your string is always in the same position - two
characters long, starting at the 14th character - and assuming the strings
are in column A and you want answers in column B, then enter into column B
the formula
=YEAR(NOW())-1900-VALUE(MID(A1;14;2))
In fact, if your strings are fixed format then using the MID function is
much easier than my previous solution using Regular Expressions.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
