I have a large column of employee IDs and I need to add the prefix "ICG-" to all of them. Is there a way to do this without manually editing every single cell? I am looking for a solution that either uses a formula, a custom cell format for visual-only changes, or a way to permanently modify the text values across the entire column.
3 answers
The quickest "permanent" way to do this is using the Ampersand (&) operator or the CONCATENATE function. In an adjacent cell, you would enter the formula ="ICG-" & A2. Once you drag this down the column, it creates the new ID. You can then copy these results and use "Paste Values" over the original column to keep the changes permanent. Alternatively, if you only want the prefix to appear visually while keeping the underlying data as just numbers, you can use Custom Number Formatting. Right-click the cells, select "Format Cells," and under the "Custom" category, type "ICG-"@. This is great for maintaining clean data for calculations while still meeting display requirements.
Have you tried using the Flash Fill feature? It’s often much faster than writing formulas for simple text additions.
If you are working with extremely large datasets, you might want to consider using Power Query. It allows you to transform columns and add prefixes as a "step" that can be refreshed whenever your source data changes.
I agree with Barbara. For complex project management trackers, Power Query is the most professional route. It keeps a record of the transformation, making the process repeatable and less prone to manual errors during monthly data updates.
Robert, Flash Fill is a lifesaver! For those who don't know, you just type the first two examples manually (e.g., ICG-101, ICG-102), and then press Ctrl + E. Excel’s AI recognizes the pattern and fills the rest of the column instantly. Just keep in mind that Flash Fill works best with static data; if your original ID changes later, the Flash-Filled version won't update automatically like a formula would.