How do I edit a generated ID to the updated column property set up

Options

One of my columns is set up with a generated ID. I have many rows of data with the original set up (ID - 23). I've changed the setup to remove spaces, so moving forward, new IDs will be formatted like ID-23. Can I go back to the existing IDs to update them with no spaces?

Answers

  • Ric T
    Ric T ✭✭✭✭✭✭
    Options

    Hi @dconrad,

    Not within the same auto-number column. To completely reset the auto-number, you'll need to delete the column, save sheet, and create a new auto-number column with corrected values.

    The other way around this is to create a helper column where you can manually c/p and modify your older entries, and create a formula for the newer entries to c/p from your existing auto-number column. You'll need

    Column 1 (helper) Column 2 (auto-number) Comment

    ID-23 ID - 23 existing IDs

    =Column 2 (output =ID-24) ID-24 new IDs

    Hope one of these options are helpful!

    Cheers,

    Ric

  • Ric T
    Ric T ✭✭✭✭✭✭
    Options

    Hi @dconrad, just realised that a screenshot may be more helpful than the makeshift table above:

    Cheers,

    Ric

  • dconrad
    dconrad ✭✭
    edited 02/29/24
    Options

    I've gone the route of creating another column and putting the correct format of the ID in that new column, but when you say to create a formula for the newer entries to copy and paste from the existing ID column... I can't put a formula in the existing ID column because (I believe) the column type is set to # Auto number. I still need to allow new entries to be added to the sheet so I need an auto number generated. And I can't change the new column with type # Auto number to start fresh there.