Forum Discussion

temporalnaut's avatar
temporalnaut
Copper Contributor
Jul 25, 2020
Solved

Numbering with prefix, without dragging

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

excel
  • HansVogelaar's avatar
    HansVogelaar
    Jul 25, 2020
    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.

8 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 25, 2020
    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.
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jul 29, 2020

      HansVogelaar 

      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.

    • temporalnaut's avatar
      temporalnaut
      Copper Contributor
      Jul 28, 2020

      HansVogelaar 

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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 28, 2020

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

Resources