Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on a formula
Hi group,
I got the following formula from Niek Otten in this group a couple of months ago. =IF(LEN(C7)=9,C7&"/1",LEFT(C7,10)&RIGHT(C7,1)+1) It works great and does exactly what I wanted but now I am getting a bit more ambitous and I'm trying to expand on it but I'm not getting anywhere fast. What the formula does is start with an input number in C7 and increments it as follows, Input C7 - EB07/1056 (e.g.) Output F12 - EB07/1056/1 F13 - EB07/1056/2 F14 - EB07/1056/3 etc. etc. I want to make two main changes to it. At present it only goes as far as /10 and then reverts to /1 I would like it to go at least as far as /20 if not further. Also I would like it to handle two different inputs at C7, the original format say EB02/0105 or alternatively E02/0105 so that the output in the first instance would be EB02/0105/1 EB02/0105/2 EB02/0105/3 And the second would be E02/0105/1 E02/0105/2 E02/0105/3 I would appreciate it if Niek or someone else could help with this cause I am not getting very far on my own, unless my goal was utter frustration, in which case I am doing remarkably well! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on a formula
=IF(LEN(C7)-LEN(SUBSTITUTE(C7,"/",""))=1,C7&"/1",LEFT(C7,FIND("/",C7,FIND("/
",C7)+1))&MID(C7,FIND("/",C7,FIND("/",C7)+1)+1,3)+1) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MartinW" wrote in message ... Hi group, I got the following formula from Niek Otten in this group a couple of months ago. =IF(LEN(C7)=9,C7&"/1",LEFT(C7,10)&RIGHT(C7,1)+1) It works great and does exactly what I wanted but now I am getting a bit more ambitous and I'm trying to expand on it but I'm not getting anywhere fast. What the formula does is start with an input number in C7 and increments it as follows, Input C7 - EB07/1056 (e.g.) Output F12 - EB07/1056/1 F13 - EB07/1056/2 F14 - EB07/1056/3 etc. etc. I want to make two main changes to it. At present it only goes as far as /10 and then reverts to /1 I would like it to go at least as far as /20 if not further. Also I would like it to handle two different inputs at C7, the original format say EB02/0105 or alternatively E02/0105 so that the output in the first instance would be EB02/0105/1 EB02/0105/2 EB02/0105/3 And the second would be E02/0105/1 E02/0105/2 E02/0105/3 I would appreciate it if Niek or someone else could help with this cause I am not getting very far on my own, unless my goal was utter frustration, in which case I am doing remarkably well! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on a formula
Thanks Bob!! I have only barely got my head around what Niek was doing with
the original formula, this one is going to take another major study tour of the help files and web sites but don't get me wrong, I'm grinning from ear to ear, it works like a dream!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on a formula
Would you like an explanation, or do you want to try yourself first?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "MartinW" wrote in message ... Thanks Bob!! I have only barely got my head around what Niek was doing with the original formula, this one is going to take another major study tour of the help files and web sites but don't get me wrong, I'm grinning from ear to ear, it works like a dream!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on a formula
Thanks for the offer Bob. Wow! The help in this group just blows me away!!!
I actually prefer to sort it out myself, it takes a lot longer but I pick up all sorts of other stuff along the way and when I get there everything is well and truly entrenched instead of just a vague memory like "Oh yeah, I do remember something like that now" Thanks again Bob. Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help With Expanding A Formula | Excel Worksheet Functions | |||
intersection formula? need help expanding on it | Excel Worksheet Functions |