• 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

R

RDEXCEL2020

Member
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.

 
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.
 
RDEXCEL2020 said:
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.
Click to expand...
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
 
@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
 
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
 
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.
 
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.
 
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.
 
Peter Bartholomew said:
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.
Click to expand...
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.
 
@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.
 
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,
 
You must log in or register to reply here.
Back
Top