SOLVED

Numbering with prefix, without dragging

%3CLINGO-SUB%20id%3D%22lingo-sub-1545341%22%20slang%3D%22en-US%22%3ENumbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545341%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20please%2C%20how%20thousands%20of%20rows%20in%20one%20column%20could%20be%20numbered%2C%20without%20dragging%20and%20with%20a%20letter%20prefix%3F%20Example%3A%3CBR%20%2F%3EAHJS1000%3CBR%20%2F%3EAHJS1001%3CBR%20%2F%3EAHJS1002%3CBR%20%2F%3E...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1545341%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1545430%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545430%22%20slang%3D%22en-US%22%3ELet's%20say%20you%20want%20to%20start%20in%20row%202.%20Select%20the%20range%2C%20starting%20in%20that%20row%2C%20that%20you%20want%20to%20number%2C%20for%20example%20A2%20to%20A100.%20Enter%20the%20formula%20%3D%22AHJS%22%26amp%3B(ROW()%2B998)%20Confirm%20with%20Ctrl%2BEnter.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547611%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1549617%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1549617%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20do%20it%20so%20that%20the%20numbers%20remain%20the%20same%20even%20after%20moving%20to%20another%20row%3F%20To%20make%20them%20static%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1551378%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1551378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F729591%22%20target%3D%22_blank%22%3E%40temporalnaut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20what%20you%20would%20get%20if%20you%20entered%20the%20first%20value%20in%20a%20cell%2C%20then%20dragged%20down.%20But%20you%20didn't%20want%20that...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552797%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20I%20have%20thousands%20of%20lines%2C%20do%20you%20know%20of%20any%20alternative%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552991%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552991%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F729591%22%20target%3D%22_blank%22%3E%40temporalnaut%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20the%20values%20in%20a%20column%20adjacent%20to%20a%20populated%20column%20that%20has%20no%20blanks%2C%20you%20can%20enter%20the%20starting%20value%20in%20the%20first%20cell%2C%20then%20double-click%20the%20fill%20handle%20in%20the%20lower%20right%20corner%20of%20the%20cell.%20Excel%20will%20automatically%20fill%20the%20column%20down%20to%20the%20last%20used%20row.%3C%2FP%3E%0A%3CP%3EAlternatively%2C%20enter%20the%20starting%20value%20in%20the%20first%20cell.%20With%20that%20cell%20still%20selected%2C%20scroll%20down%20and%20then%20hold%20down%20Shift%20while%20selecting%20the%20last%20cell.%20On%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Fill%20%26gt%3B%20Flash%20Fill.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1553318%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1553318%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1553643%22%20slang%3D%22en-US%22%3ERE%3A%20Numbering%20with%20prefix%2C%20without%20dragging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1553643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPower%20Query%20was%20built%20to%20transform%20data.%26nbsp%3B%20It%20will%20also%20push%20one%20towards%20the%20use%20of%20Tables%20which%20is%20probably%20the%20No1%20improvement%20that%20can%20be%20made%20to%20any%20data%20analysis%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi, please, how thousands of rows in one column could be numbered, without dragging and with a letter prefix? Example:
AHJS1000
AHJS1001
AHJS1002
...

 

thank you

8 Replies
best response confirmed by temporalnaut (Contributor)
Solution
Let's say you want to start in row 2. Select the range, starting in that row, that you want to number, for example A2 to A100. Enter the formula ="AHJS"&(ROW()+998) Confirm with Ctrl+Enter.

@Hans Vogelaar 

How to do it so that the numbers remain the same even after moving to another row? To make them static values.

@temporalnaut 

That is what you would get if you entered the first value in a cell, then dragged down. But you didn't want that...

@Hans Vogelaar 

Because I have thousands of lines, do you know of any alternative?

@temporalnaut 

If you want the values in a column adjacent to a populated column that has no blanks, you can enter the starting value in the first cell, then double-click the fill handle in the lower right corner of the cell. Excel will automatically fill the column down to the last used row.

Alternatively, enter the starting value in the first cell. With that cell still selected, scroll down and then hold down Shift while selecting the last cell. On the Home tab of the ribbon, click Fill > Flash Fill.

@Hans Vogelaar 

Thank you very much

@Hans Vogelaar 

Power Query was built to transform data.  It will also push one towards the use of Tables which is probably the No1 improvement that can be made to any data analysis workbook.