Excel incrementing

%3CLINGO-SUB%20id%3D%22lingo-sub-1745446%22%20slang%3D%22en-US%22%3EExcel%20incrementing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1745446%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20please%20tell%20me%20how%20to%20increment%20by%201%20the%20numbers%20only%20when%20you%20have%20letters%20with%20the%20numbers%3F%20For%20example%3A%20I%20have%20B-001A%20and%20need%20to%20increment%20the%20001%20by%201%2C%20so%20that%20when%20I%20drag%20it%20down%20I%20get%20B-002A%2C%20B-003A%2C%20etc.%20I%20know%20how%20to%20do%20it%20with%20just%20numbers%2C%20very%20easy%20but%20need%20help%20when%20there%20are%20letters%20involved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1745446%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1745501%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20incrementing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1745501%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F820457%22%20target%3D%22_blank%22%3E%40Chris405%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20you%20try%20this%20formula%2C%20you%20only%20have%20to%20adapt%20it%20to%20your%20needs%20or%20cells.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLEFT(A1%2C3)%20%26amp%3B%20RIGHT(A1%2C5)%2B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1745504%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20incrementing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1745504%22%20slang%3D%22de-DE%22%3EYou%20could%20also%20solve%20it%20with%20a%20VBA%20procedure%3A%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Sub%20increment%20()%20%3CBR%20%2F%3E%20Range%20(%22A1%22).%20Select%20'A1%20contains%20e.g.%200AK20001%20%3CBR%20%2F%3E%20Selection.AutoFill%20Destination%3A%20%3D%20Range%20(%22A1%3A%20A30%22)%20'The%20area%20A1%3A%20A30%20is%20filled%20out%20so%20that%200AK20030%20is%20in%20A30.%3CBR%20%2F%3EEnd%20Sub%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20You%20would%20have%20to%20adjust%20the%20area.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20*%20It's%20always%20good%20to%20add%20your%20operating%20system%20and%20Excel%20version.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E
Occasional Visitor

Can someone please tell me how to increment by 1 the numbers only when you have letters with the numbers? For example: I have B-001A and need to increment the 001 by 1, so that when I drag it down I get B-002A, B-003A, etc. I know how to do it with just numbers, very easy but need help when there are letters involved.

 

Thanks

4 Replies

@Chris405 

If you try this formula, you only have to adapt it to your needs or cells.

 

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

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

You could also solve it with a VBA procedure:

Sub increment ()
Range ("A1"). Select 'A1 contains e.g. 0AK20001
Selection.AutoFill Destination: = Range ("A1: A30") 'The area A1: A30 is filled out so that 0AK20030 is in A30.
End Sub

You would have to adjust the area.

* It's always good to add your operating system and Excel version.

I would be happy to know if I could help.

Nikolino
I know I don't know anything (Socrates)

@Chris405 

Not what you want. But a 3:d cheating version

Put numbers in column A 

=ROW()

and use the number-format "00#"

at least 2 rows of Exemple data in B

Select B1:B9 press Ctrl+E for excels "intelligent" Flash Fill

001B-001A
002B-002A
003 
004 
005 
006 
007 
008 
009 

 

@Deleted 

 

just a suggestionjust a suggestion

 

Nikolino

I know I don't know anything (Socrates)