Expanding on a formula

[MartinW]

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!

 

[Bob Phillips]

=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]

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!!!

 

[Bob Phillips]

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]

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