• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consecutive number with Letter in Front

Hello,

I need a code that make my number with letter to be consecutive.

Let say A1 I have enter S701 and in A5 I need a code to make my number show S702 and so on. I will placing the code in a different cell.

Something like this
=IF(N(A1),A1+1,"")
but this code works only with numbers with no letter.




Thanks.

 

Peter Bartholomew

Well-Known Member
The following strips out the text, increments the count, and re-concatenates text and count
= IF(A1<>"", "S"&(1+SUBSTITUTE(A1,"S","")), "")
As a 365 user, though, I would build the codes dynamically using the SEQUENCE function.
 

shrivallabha

Excel Ninja
Hello,

I need a code that make my number with letter to be consecutive.

Let say A1 I have enter S701 and in A5 I need a code to make my number show S702 and so on. I will placing the code in a different cell.

Something like this
=IF(N(A1),A1+1,"")
but this code works only with numbers with no letter.




Thanks.
If your requirement is limited to displaying alone then you can continue to use your formula by applying a custom cell formatting as ""S"General" (bold portion without quotes)
70335
 

Peter Bartholomew

Well-Known Member
@Kenshin
Interesting formula, though, to generalise to arbitrary length strings,
= LEFT(A1, MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},A1&"0123456789"))-1)
& MID(A1, MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},A1&"0123456789")),10)+1
 

Peter Bartholomew

Well-Known Member
All dressed up with Excel365 beta
Code:
= LET(
   priorCode, $A1,
   len, MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},
        priorCode&"0123456789"))-1,
   string, LEFT(priorCode, len),
   seq, 1+ MID(priorCode, len+1, 10),
   code, string&seq,
   IF(priorCode<>"",code,"") )
or Khalid's simpler rendition
Code:
= LET(
  priorCode, $A9,
  string, LEFT(priorCode),
  seq, 1+ MID(priorCode, 2, 10),
  code, string&seq,
  IF(priorCode<>"",code,"") )
I wonder what Dan Bricklin thinks of 'modern Excel'?

shrivallabha
I use number formatting frequently, e.g.
"Yes";"Yes";"No";@\?
but sometimes wonder what an end user will make of it.
 
Last edited:
Hi guys,
So I tried Khalid code. I think this one works. But I have an issue.
I meant to start from S01 but when it appear in the other cell it appeared S2, S3, S4 instead of S02, S03, S04.
How to change it from this code: =LEFT(A1)&MID(A1,2,LEN(A1))+1
 

Peter Bartholomew

Well-Known Member
This assumes any string, here "S-", followed by a 2-digit number, with null strings on the intervening rows:
= IF( A1<>"", "S-"&TEXT(1+RIGHT(A1,2),"00"), "")

A note in case anyone is interested. If you use a defined name 'prior' to refer to the relative reference A1, then the formula may start in cell A2 without a #REF! error. Within a defined name the reference will simply wrap to the bottom of the sheet.
 
Last edited:
Hi

Okey… Peter I like this code. Seems works very well.

= IF( A1<>"", "S"&TEXT(1+RIGHT(A1,2),"00"), "")

Work on S01 thru S099 but the problem is when I reach S100.

My issue is that when I reach to 100 as my next number it appeared S01 instead S101.

S01-S099 is perfect. So I need to change to this part +RIGHT(A1,2) to +RIGHT(A1,3). From ,2) to ,3) to get the correct value.

So is there anyway to set it to where I can enter S01 thru S2000 just in case I reach to thousand digits?

I want to set this code in a cells where its placed. The cells will be protected except A1 cell. The user will not be able to change the code once I protected the worksheet.



Thanks.
 

Peter Bartholomew

Well-Known Member
Some of the other replies had considerable flexibility built in, whereas I finished up with a solution very much with the assumptions hard-wired into the formula. To run with 4 digit numbers requires
= IF( A1<>"", "S"&TEXT(1+RIGHT(A1,4),"0000"), "")
[changes in red]

As an alternative, you could still revert to @shrivallabha's idea and use the number format "\S0000"
 
Last edited:
Hi
I actually did try on all the codes from others but unfortunately did not get what I need. Thanks though to all.

So with your code, Do I actually need to keep changing the if my digit are 2, or 3 digits or 4 digits? Can't it stay to 4 digits and still be able to enter 2 digits such as S01 , S02 , S100, S1000... something like this.
I am trying to avoid to keep changing the code because I want to protect the worksheet for the other users. All they have to enter is the number in A1 and the rest of the cells will be protected. I want them to be able to enter many digits as they can such as S01, S02, etc.

Hopefully its possible.

Thanks.
 

Peter Bartholomew

Well-Known Member
If you are going for text codes, I would recommend sticking with a fixed number of digits. Otherwise you have the problem that S320 is a code lying between S2 and S52; sorting or searching is a mess. If you want variable length codes then that is covered in #6. Number formats are covered in #4.
 

shrivallabha

Excel Ninja
All dressed up with Excel365 beta
Code:
= LET(
   priorCode, $A1,
   len, MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},
        priorCode&"0123456789"))-1,
   string, LEFT(priorCode, len),
   seq, 1+ MID(priorCode, len+1, 10),
   code, string&seq,
   IF(priorCode<>"",code,"") )
or Khalid's simpler rendition
Code:
= LET(
  priorCode, $A9,
  string, LEFT(priorCode),
  seq, 1+ MID(priorCode, 2, 10),
  code, string&seq,
  IF(priorCode<>"",code,"") )
I wonder what Dan Bricklin thinks of 'modern Excel'?

shrivallabha
I use number formatting frequently, e.g.
"Yes";"Yes";"No";@\?
but sometimes wonder what an end user will make of it.
Depends on who the end user really is ;) Sometimes, the sheets are being made by the end-users themselves.

People come up with all sorts of requirements which are "unclear/unsaid" in their initial posts. Just when you think that the thread is solved the pole shifts. e.g. in this case it is apparently a sequence but later the OP may ask for some math operation which will again require getting rid of the alphabet portion.

So usually if I know an approach and reason (i.e. you are simply interested in visual data), I post it.
EDIT: Looks like OP has shifted poles.
 

shrivallabha

Excel Ninja
@RDEXCEL2020
Your requirement is still unclear to me (maybe many...)

If you could provide a clear rule.
Single digit --> S01 or S1
Double digit --> S099 or S99
Triple digit --> S0999 or S999
Quadruple digit --> S09999 or S9999

and so on then it will be much easier to understand.

The impression I got is you only need digits 1 to 9 to be padded to be 01,02 etc and rest as they are prefixed by S.
 

Khalid NGO

Excel Ninja
Hi,

Please check this:

=LEFT(A1)&TEXT(MID(A1,2,LEN(A1))+1,REPT(0,LEN(A1)-1))

Again it is based on your provided pattern, it is best to include all possible inputs in initial post, so that members can share targeted solutions.

Regards,
 
Top