Incremental number increase in alphanumeric text

[tanyaslater]

I am looking for a formula (preferably a formula rather than a macro as generally whenever I run a macro it crashes Excel and I lose all my changes) that will allow me to incrementally increase a number within a string of alphanumeric text horizontally across columns.

I have a column of serial numbers for a range of items that apply to 1 part of a construction. That construction is then replicated so i need to replicate the serial numbers but incrementally increase the number that indicates the order of construction i.e. my first column is as below:

CUB-1-F1

CUB-1-FA

CUB-1-FB

CUB-1-FC

CUB-1-B1

CUB-1-B2

CUB-1-B3

CUB-1-B4

<!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

the next column needs to be identical with the exception of the fact that I need to replace the first 1 with a 2 i.e. CUB - 1 becomes CUB-2

CUB-2-F1

CUB-2-FA

CUB-2-FB

CUB-2-FC

CUB-2-B1

CUB-2-B2

CUB-2-B3

CUB-2-B4

<!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

I've tried to drag the handle across but it just copies and replicates the first two columns and I can't figure out how to incrementally increase the number.

Any hlep would be much appreciated.

 

[Fishboy]

Hi Tanya, welcome to the boards.

Assuming that CUB-1-F1 is in cell A1 of your worksheet, try this in cell A2, then drag across:

=LEFT(A1,4)&(MID(A1,5,1)+1)&RIGHT(A1,3)

[EDIT] - This only works so long as the number in the middle doesn't need to go above 10, e.g CUB-10-F1 is the end. If it has to go higher than 10 you will need an alternative formula instead of this one.

 

[tanyaslater]

Hey there

Thanks so much for the response but unfortunately I do need it to go higher than 10 - I need to extend it up to 49.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is the prefix ("CUB" in this case) always 3 characters long?
Is the suffix always 2 characters long as in your sample?

 

[tanyaslater]

Hi there

Yes the prefix is always 3 letters long but the suffix can vary depending on the item

Many thanks

Tanya

 

[Peter_SSs]

Hi there

Yes the prefix is always 3 letters long ...

In that case try this copied across & down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1
2	CUB-1-F1	CUB-2-F1	CUB-3-F1	CUB-4-F1	CUB-5-F1	CUB-6-F1	CUB-7-F1	CUB-8-F1	CUB-9-F1	CUB-10-F1	CUB-11-F1
3	CUB-1-FA	CUB-2-FA	CUB-3-FA	CUB-4-FA	CUB-5-FA	CUB-6-FA	CUB-7-FA	CUB-8-FA	CUB-9-FA	CUB-10-FA	CUB-11-FA
4	CUB-1-FB	CUB-2-FB	CUB-3-FB	CUB-4-FB	CUB-5-FB	CUB-6-FB	CUB-7-FB	CUB-8-FB	CUB-9-FB	CUB-10-FB	CUB-11-FB
5	CUB-1-FC	CUB-2-FC	CUB-3-FC	CUB-4-FC	CUB-5-FC	CUB-6-FC	CUB-7-FC	CUB-8-FC	CUB-9-FC	CUB-10-FC	CUB-11-FC
6	CUB-1-B1	CUB-2-B1	CUB-3-B1	CUB-4-B1	CUB-5-B1	CUB-6-B1	CUB-7-B1	CUB-8-B1	CUB-9-B1	CUB-10-B1	CUB-11-B1
7	CUB-1-B2	CUB-2-B2	CUB-3-B2	CUB-4-B2	CUB-5-B2	CUB-6-B2	CUB-7-B2	CUB-8-B2	CUB-9-B2	CUB-10-B2	CUB-11-B2
8	CUB-1-B3	CUB-2-B3	CUB-3-B3	CUB-4-B3	CUB-5-B3	CUB-6-B3	CUB-7-B3	CUB-8-B3	CUB-9-B3	CUB-10-B3	CUB-11-B3
9	CUB-1-B4	CUB-2-B4	CUB-3-B4	CUB-4-B4	CUB-5-B4	CUB-6-B4	CUB-7-B4	CUB-8-B4	CUB-9-B4	CUB-10-B4	CUB-11-B4
Increment

 

[tanyaslater]

Thank you thank you thank you so much.

That is exactly what I needed


So grateful!!!!!



Excel tables to the web >> Excel Jeanie HTML 4 [/QUOTE]

 

[bosco_yip]

Another option :

In B2, copy across & down :

=SUBSTITUTE($A2,"-1","-"&COLUMNS($A2:B2))

Regards

 

[Peter_SSs]

Another option :

In B2, copy across & down :

=SUBSTITUTE($A2,"-1","-"&COLUMNS($A2:B2))

Regards

This looks good to me provided the "middle number" always starts at 1 - which it seems is the case.
Two remarks:

We are dealing with numbers so we could eliminate the quote marks & concatenation.

Given that the final part of the codes do contain numbers, just in case you might ever get an initial code like "CUB-1-1F", it might be worth adding the final argument to ensure only 1 substitution is made.

So possibly
=SUBSTITUTE($A2,-1,-COLUMNS($A2:B2),1)

 

[István Hirsch]

The number to increase never has leading zero(s), for example CUB-01-FA?