PLEASE REMOVE ME FROM YOUR MAIL LIST
THANKS
----- Original Message -----
From: "Harold Fuchs" <[email protected]>
To: <[email protected]>
Sent: Saturday, September 26, 2009 6:10 AM
Subject: Re: [users] number extraction for a formula
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]
---------------------------------------------------------------------------------------------------
Text inserted by Panda GP 2009:
This message has NOT been classified as spam. If it is unsolicited mail
(spam), click on the following link to reclassify it:
http://localhost:6083/Panda?ID=pav_523&SPAM=true&path=C:\Windows\system32\config\systemprofile\AppData\Local\Panda%20Security\Panda%20Global%20Protection%202009\AntiSpam
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
