Single Formula

[Jesan]

Hello,

I would like to ask if there is a single formula wherein I change the value of "A" to "B" to "C" and "D" for the ff. I try the SUBSTITUTE but I do it one by one.

KIT-1234567-01-A
KIT-1234567-01-B
KIT-1234567-01-C
KIT-1234567-01-D


Thank you so much,
San

 

[Alex Blakenburg]

Assuming that was a real example and not a simplification, try this:
If your data doesn't start in A1 you will need to change the reference.

=SUBSTITUTE(A1,"-"&RIGHT(A1,1),"-"&CHAR(CODE(RIGHT(A1,1))+1))

 

[cmowla]

Another possibility: (Where you subtract 6 in this example, because that's the row number that the lettering starts.)

Blank33.xlsb
	A
1
2
3
4
5
6	KIT-1234567-01-A
7	KIT-1234567-01-B
8	KIT-1234567-01-C
9	KIT-1234567-01-D
10	KIT-1234567-01-E
11	KIT-1234567-01-F
12	KIT-1234567-01-G
13	KIT-1234567-01-H
14	KIT-1234567-01-I
15	KIT-1234567-01-J
16	KIT-1234567-01-K
17	KIT-1234567-01-L
18	KIT-1234567-01-M
19	KIT-1234567-01-N
20	KIT-1234567-01-O
21	KIT-1234567-01-P
Sheet6

Cell Formulas
Range	Formula
A6:A21	A6	=CONCATENATE("KIT-1234567-01-",SUBSTITUTE(SUBSTITUTE(ADDRESS(1,ROW()-6+1),"$",""),"1",""))

And I'm not sure if you want it to count up to AA after Z or go back to A after you get to Z or if you just want it to increment up a letter for just those 4 values. But if you want it to go back to A after it gets to Z (be in a continous loop), then:

Blank33.xlsb
	A
1	KIT-1234567-01-A
2	KIT-1234567-01-B
3	KIT-1234567-01-C
4	KIT-1234567-01-D
5	KIT-1234567-01-E
6	KIT-1234567-01-F
7	KIT-1234567-01-G
8	KIT-1234567-01-H
9	KIT-1234567-01-I
10	KIT-1234567-01-J
11	KIT-1234567-01-K
12	KIT-1234567-01-L
13	KIT-1234567-01-M
14	KIT-1234567-01-N
15	KIT-1234567-01-O
16	KIT-1234567-01-P
17	KIT-1234567-01-Q
18	KIT-1234567-01-R
19	KIT-1234567-01-S
20	KIT-1234567-01-T
21	KIT-1234567-01-U
22	KIT-1234567-01-V
23	KIT-1234567-01-W
24	KIT-1234567-01-X
25	KIT-1234567-01-Y
26	KIT-1234567-01-Z
27	KIT-1234567-01-A
28	KIT-1234567-01-B
29	KIT-1234567-01-C
Sheet6

Cell Formulas
Range	Formula
A1:A29	A1	=CONCATENATE("KIT-1234567-01-",MID(IFERROR(ADDRESS(1,MOD(ROW(),26)),"$Z"),2,1))

 

[Jesan]

Thank you very much for your answers. Its all working. Awesome.

 

[Alex Blakenburg]

Thank you for letting us know. Glad we could help.