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" <mtmw@hotmail.invalid> wrote in message
news:e$Qh37EZGHA.4060@TK2MSFTNGP02.phx.gbl...
> 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!
>
>

[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" <mtmw@hotmail.invalid> wrote in message
news:uob$GfFZGHA.3936@TK2MSFTNGP05.phx.gbl...
> 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!!!
>
>

[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