I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
etc.

Thanks.

Try this:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")

HTH
--
AP

"Mel" a écrit dans le message de news:
...
I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc.
How
can I do this automatically? Also Invoice Numbers eg ms/0988/06,
ms/0989/06
etc.

Thanks.

Hi - sorry I failed! It returns #VALUE! Not sure what that means. I
should've maybe mentioned the numbers go down in a column? I'm a relatively
new user so appreciate your help!

"Ardus Petus" wrote:

Try this:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")

HTH
--
AP

"Mel" a écrit dans le message de news:
...
I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc.
How
can I do this automatically? Also Invoice Numbers eg ms/0988/06,
ms/0989/06
etc.

Thanks.

Enter your initial value in A1:
8987/010942
Enter my formula in A2:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")
You should get the expected result of:
8988/010943
You can now drag down my formula to get the following sequence numbers.

If your initial value is no t in A1 but in any other cell, just replace A1
with that other cell's address in my formula.

HTH
--
AP

"Mel" a écrit dans le message de news:
...
Hi - sorry I failed! It returns #VALUE! Not sure what that means. I
should've maybe mentioned the numbers go down in a column? I'm a
relatively
new user so appreciate your help!

"Ardus Petus" wrote:

Try this:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")

HTH
--
AP

"Mel" a écrit dans le message de news:
...
I have a two part number for tracking delivery notes, separated by a
slash
and each number increasing sequentially eg 8987/010942, 8988/010943
etc.
How
can I do this automatically? Also Invoice Numbers eg ms/0988/06,
ms/0989/06
etc.

Thanks.

et voila! Thanks very much, now to understand the formula!

"Ardus Petus" wrote:

Enter your initial value in A1:
8987/010942
Enter my formula in A2:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")
You should get the expected result of:
8988/010943
You can now drag down my formula to get the following sequence numbers.

If your initial value is no t in A1 but in any other cell, just replace A1
with that other cell's address in my formula.

HTH
--
AP

"Mel" a écrit dans le message de news:
...
Hi - sorry I failed! It returns #VALUE! Not sure what that means. I
should've maybe mentioned the numbers go down in a column? I'm a
relatively
new user so appreciate your help!

"Ardus Petus" wrote:

Try this:
=LEFT(A1,SEARCH("/",A1)-1)+1&"/"&TEXT(RIGHT(A1,LEN(A1)-SEARCH("/",A1))+1,"000000")

HTH
--
AP

"Mel" a écrit dans le message de news:
...
I have a two part number for tracking delivery notes, separated by a
slash
and each number increasing sequentially eg 8987/010942, 8988/010943
etc.
How
can I do this automatically? Also Invoice Numbers eg ms/0988/06,
ms/0989/06
etc.

Thanks.

Without using too complicated formulas:

Insert a column for the first part of the number ( e.g. column A) and a
column for the second part of your number ( e.g. Column B) and in the third
column use the formula =A1&"/"&B1

Sequential numbering can the be arranged in colums A and B

Hans

"Mel" wrote:

I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
etc.

Thanks.

Thanks - but that doesn't seem to work, says there's an error in the formula?


"hans bal(nl)" wrote:

Without using too complicated formulas:

Insert a column for the first part of the number ( e.g. column A) and a
column for the second part of your number ( e.g. Column B) and in the third
column use the formula =A1&"/"&B1

Sequential numbering can the be arranged in colums A and B

Hans

"Mel" wrote:

I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
etc.

Thanks.

=A1&"/"&B1 works in my Excel, but you can also try : =concatenate(A1;"/";B1)
( ; is the list separator, depending on your regional settings you ay have
to replace it by a , )




"Mel" wrote:

Thanks - but that doesn't seem to work, says there's an error in the formula?


"hans bal(nl)" wrote:

Without using too complicated formulas:

Insert a column for the first part of the number ( e.g. column A) and a
column for the second part of your number ( e.g. Column B) and in the third
column use the formula =A1&"/"&B1

Sequential numbering can the be arranged in colums A and B

Hans

"Mel" wrote:

I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
etc.

Thanks.

Hi - ok that worked (with the ,) but when I drag down it fills the cells with
the same number?

"hans bal(nl)" wrote:

=A1&"/"&B1 works in my Excel, but you can also try : =concatenate(A1;"/";B1)
( ; is the list separator, depending on your regional settings you ay have
to replace it by a , )




"Mel" wrote:

Thanks - but that doesn't seem to work, says there's an error in the formula?


"hans bal(nl)" wrote:

Without using too complicated formulas:

Insert a column for the first part of the number ( e.g. column A) and a
column for the second part of your number ( e.g. Column B) and in the third
column use the formula =A1&"/"&B1

Sequential numbering can the be arranged in colums A and B

Hans

"Mel" wrote:

I have a two part number for tracking delivery notes, separated by a slash
and each number increasing sequentially eg 8987/010942, 8988/010943 etc. How
can I do this automatically? Also Invoice Numbers eg ms/0988/06, ms/0989/06
etc.

Thanks.

Set calculations to automatic: Tools/Options/Calculation

In article ,
Mel wrote:

Hi - ok that worked (with the ,) but when I drag down it fills the cells with
the same number?