If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
extracting numbers
On Mon, 6 Feb 2006 17:18:28 -0800, "cj" wrote:
oops, didn't install morefunc. it works now, thanks. is there any way i can include the words cash|office|stock to make a2 add up the numbers only if any of those 3 dept are entered in a1? Yes, there is. But do you have any more specifications? --ron |
#22
|
|||
|
|||
extracting numbers
On Mon, 06 Feb 2006 21:17:25 -0500, Ron Rosenfeld
wrote: On Mon, 6 Feb 2006 17:18:28 -0800, "cj" wrote: oops, didn't install morefunc. it works now, thanks. is there any way i can include the words cash|office|stock to make a2 add up the numbers only if any of those 3 dept are entered in a1? Yes, there is. But do you have any more specifications? --ron This is one way: =IF(REGEX.FIND(A1,"cash|office|stock")0,EVAL(REGE X.SUBSTITUTE(A1&"0","(\D+)","+")),"") But it'll be easiest if you could lay out all of your specifications at once, for your project. --ron |
#23
|
|||
|
|||
extracting numbers
one last specification for the formula,if column a1 is "3cash 4office 1train"
,i want a2 which is the cash|office hrs column to add only the 3cash 4office is = 7 and a3 which is the train|hiring|other hrs column to add only the 1train is = 1. i have 6 different hrs columns with 3 or more different depts. the order of depts can vary in order and combination. eg) a1 shift worked column a1= 3cash 4office 1other a1=3stock 2train 2pay a2 cash|office|pay column a2= 7 a2=2 a3 stock|train|rec column a3= 0 a3=5 a4 other|front|ret|rel column a4=1 a4=0 "Ron Rosenfeld" wrote: On Mon, 06 Feb 2006 21:17:25 -0500, Ron Rosenfeld wrote: On Mon, 6 Feb 2006 17:18:28 -0800, "cj" wrote: oops, didn't install morefunc. it works now, thanks. is there any way i can include the words cash|office|stock to make a2 add up the numbers only if any of those 3 dept are entered in a1? Yes, there is. But do you have any more specifications? --ron This is one way: =IF(REGEX.FIND(A1,"cash|office|stock")0,EVAL(REGE X.SUBSTITUTE(A1&"0","(\D+)","+")),"") But it'll be easiest if you could lay out all of your specifications at once, for your project. --ron |
#24
|
|||
|
|||
extracting numbers
On Tue, 7 Feb 2006 12:59:29 -0800, "cj" wrote:
one last specification for the formula,if column a1 is "3cash 4office 1train" ,i want a2 which is the cash|office hrs column to add only the 3cash 4office is = 7 and a3 which is the train|hiring|other hrs column to add only the 1train is = 1. i have 6 different hrs columns with 3 or more different depts. the order of depts can vary in order and combination. eg) a1 shift worked column a1= 3cash 4office 1other a1=3stock 2train 2pay a2 cash|office|pay column a2= 7 a2=2 a3 stock|train|rec column a3= 0 a3=5 a4 other|front|ret|rel column a4=1 a4=0 If this is all you've wanted, we've wasted a lot of time. In your first post, you indicated that the numbers would be 1-8. And you've always had the strings formatted with no space between the number and the description. All you need to do to pull out the number associated with a particular descriptor is: =MID(A1,FIND(descriptor,A1)-1,1) or, for the office: =MID(A1,FIND("office",A1)-1,1) And you can use the add operator to add these together. So if you wanted to add only the cash and office from a string "3cash 4office 1train" all you need to do is: =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1) You should be able to figure out the rest by yourself. --ron |
#25
|
|||
|
|||
extracting numbers
hi ron
thanks for your answers and patience, i really appreciate your help. i play with the formulas a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1, "stock|train|rec|st|tr")0,EVAL(REGEX.SUBSTITUTE(a 1&"0","(\D+)","+")),"")) a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1," cash|office|pay|cs|off|py")0,EVAL(REGEX.SUBSTITUT E(a1&"0","(\D+)","+")),"")) these 2 formulas work great: when i enter in a1 just "cash" a2 returns with 8 and same for cash. thats what i want and i can add extra words if the shift is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock", both a2 and a3 return with 8. its there any way can separate them. i need a2 to return with 2 and a3 to return with 5? thanks "Ron Rosenfeld" wrote: On Tue, 7 Feb 2006 12:59:29 -0800, "cj" wrote: one last specification for the formula,if column a1 is "3cash 4office 1train" ,i want a2 which is the cash|office hrs column to add only the 3cash 4office is = 7 and a3 which is the train|hiring|other hrs column to add only the 1train is = 1. i have 6 different hrs columns with 3 or more different depts. the order of depts can vary in order and combination. eg) a1 shift worked column a1= 3cash 4office 1other a1=3stock 2train 2pay a2 cash|office|pay column a2= 7 a2=2 a3 stock|train|rec column a3= 0 a3=5 a4 other|front|ret|rel column a4=1 a4=0 If this is all you've wanted, we've wasted a lot of time. In your first post, you indicated that the numbers would be 1-8. And you've always had the strings formatted with no space between the number and the description. All you need to do to pull out the number associated with a particular descriptor is: =MID(A1,FIND(descriptor,A1)-1,1) or, for the office: =MID(A1,FIND("office",A1)-1,1) And you can use the add operator to add these together. So if you wanted to add only the cash and office from a string "3cash 4office 1train" all you need to do is: =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1) You should be able to figure out the rest by yourself. --ron |
#26
|
|||
|
|||
extracting numbers
I see in your earlier post this is for a timesheet.
I would highly recommend you redesign things so that you don't have to use such "hacked" formulas to account for time worked. My suggestion still stands! Use *1* cell for hours worked and *1* cell for the dept. Biff "cj" wrote in message ... hi ron thanks for your answers and patience, i really appreciate your help. i play with the formulas a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1, "stock|train|rec|st|tr")0,EVAL(REGEX.SUBSTITUTE(a 1&"0","(\D+)","+")),"")) a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1," cash|office|pay|cs|off|py")0,EVAL(REGEX.SUBSTITUT E(a1&"0","(\D+)","+")),"")) these 2 formulas work great: when i enter in a1 just "cash" a2 returns with 8 and same for cash. thats what i want and i can add extra words if the shift is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock", both a2 and a3 return with 8. its there any way can separate them. i need a2 to return with 2 and a3 to return with 5? thanks "Ron Rosenfeld" wrote: On Tue, 7 Feb 2006 12:59:29 -0800, "cj" wrote: one last specification for the formula,if column a1 is "3cash 4office 1train" ,i want a2 which is the cash|office hrs column to add only the 3cash 4office is = 7 and a3 which is the train|hiring|other hrs column to add only the 1train is = 1. i have 6 different hrs columns with 3 or more different depts. the order of depts can vary in order and combination. eg) a1 shift worked column a1= 3cash 4office 1other a1=3stock 2train 2pay a2 cash|office|pay column a2= 7 a2=2 a3 stock|train|rec column a3= 0 a3=5 a4 other|front|ret|rel column a4=1 a4=0 If this is all you've wanted, we've wasted a lot of time. In your first post, you indicated that the numbers would be 1-8. And you've always had the strings formatted with no space between the number and the description. All you need to do to pull out the number associated with a particular descriptor is: =MID(A1,FIND(descriptor,A1)-1,1) or, for the office: =MID(A1,FIND("office",A1)-1,1) And you can use the add operator to add these together. So if you wanted to add only the cash and office from a string "3cash 4office 1train" all you need to do is: =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1) You should be able to figure out the rest by yourself. --ron |
#27
|
|||
|
|||
extracting numbers
On Tue, 7 Feb 2006 20:48:13 -0800, "cj" wrote:
hi ron thanks for your answers and patience, i really appreciate your help. i play with the formulas a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1 ,"stock|train|rec|st|tr")0,EVAL(REGEX.SUBSTITUTE( a1&"0","(\D+)","+")),"")) a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1, "cash|office|pay|cs|off|py")0,EVAL(REGEX.SUBSTITU TE(a1&"0","(\D+)","+")),"")) these 2 formulas work great: when i enter in a1 just "cash" a2 returns with 8 and same for cash. thats what i want and i can add extra words if the shift is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock", both a2 and a3 return with 8. its there any way can separate them. i need a2 to return with 2 and a3 to return with 5? Reread my last post and use the formulas there. For what you've finally described, you don't need morefunc or the REGEX functions at all. =========================== All you need to do to pull out the number associated with a particular descriptor is: =MID(A1,FIND(descriptor,A1)-1,1) or, for the office: =MID(A1,FIND("office",A1)-1,1) And you can use the add operator to add these together. So if you wanted to add only the cash and office from a string "3cash 4office 1train" all you need to do is: =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1) You should be able to figure out the rest by yourself. ============================== --ron |
#28
|
|||
|
|||
extracting numbers
ron
this formula =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1) works fine if i enter in a1 : 3cash 2office 1train or 3cash 2office,but returns with #value! error when i enter just 8cash or left blank for that day off. with the regex formula i can enter "-" for the blank day and my totals on the bottom still works and i can change and add combination of the words cash|cs|office|of and i can add extra words after them "3cash training 2office(front). "Ron Rosenfeld" wrote: On Tue, 7 Feb 2006 20:48:13 -0800, "cj" wrote: hi ron thanks for your answers and patience, i really appreciate your help. i play with the formulas a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1 ,"stock|train|rec|st|tr")0,EVAL(REGEX.SUBSTITUTE( a1&"0","(\D+)","+")),"")) a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1, "cash|office|pay|cs|off|py")0,EVAL(REGEX.SUBSTITU TE(a1&"0","(\D+)","+")),"")) these 2 formulas work great: when i enter in a1 just "cash" a2 returns with 8 and same for cash. thats what i want and i can add extra words if the shift is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock", both a2 and a3 return with 8. its there any way can separate them. i need a2 to return with 2 and a3 to return with 5? Reread my last post and use the formulas there. For what you've finally described, you don't need morefunc or the REGEX functions at all. =========================== All you need to do to pull out the number associated with a particular descriptor is: =MID(A1,FIND(descriptor,A1)-1,1) or, for the office: =MID(A1,FIND("office",A1)-1,1) And you can use the add operator to add these together. So if you wanted to add only the cash and office from a string "3cash 4office 1train" all you need to do is: =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1) You should be able to figure out the rest by yourself. ============================== --ron |
#29
|
|||
|
|||
extracting numbers
On Wed, 8 Feb 2006 11:26:40 -0800, "cj" wrote:
ron this formula =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1) works fine if i enter in a1 : 3cash 2office 1train or 3cash 2office,but returns with #value! error when i enter just 8cash or left blank for that day off. with the regex formula i can enter "-" for the blank day and my totals on the bottom still works and i can change and add combination of the words cash|cs|office|of and i can add extra words after them "3cash training 2office(front). I see what you mean. And I'm glad you've got something working for yourself. To return a zero when the descriptor does not exist, you could also use a formula like: =REGEX.MID(A1&"0","\d(?=descriptor|$)") or, for cash: =REGEX.MID(A1&"0","\d(?=cash|$)") or, to allow for the possibility of a space between the number and the descriptor: =REGEX.MID(A1&"0","\d(?=\s*(descriptor|$))") --ron |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Numbers stored as text causes problem with VLOOKUP | bpeltzer | Worksheet Functions | 0 | February 4th, 2006 08:07 PM |
How to make excel not round real numbers when making a histogram? | Leedawg | Charts and Charting | 1 | September 21st, 2005 07:36 PM |
Extracting numbers from a cell | morchard | General Discussion | 2 | June 30th, 2005 10:41 AM |
extracting numbers | Trevor | General Discussion | 2 | November 4th, 2004 03:12 AM |