Forum Discussion
Adding letters to an existing column of numbers
I need to know how I can add letters to an existing column of numbers. I need to add the same two letters before each group of numbers. Flash fill is not doing it for me, or I'm doing it wrong.
Hi Katstorm
You can use two options to add letters with numbers, one is through custom format as mentioned below: Simply select the whole column (or required rows) and define custom format as "AB"###0
Alternatively you can use Concat() function to add desired text with numbers.
Thanks
Tauqeer
I just want to f/u on tauqeeracma 's excellent suggestions.
The 1st option using Custom Number Format is quick & easy but has additional benefit/potential issue to note. Using this Custom Number Format will ONLY change how those cells are DISPLAYED. That means the VALUE in the cell is still the same. This is great if you might have other cells that you want to perform math on those values (e.g. =IF(A3+5>=A4, ... ) but if you want to use those cells in a lookup/comparison (e.g. =VLOOKUP(A3, ... ) you need to realize those letter prefix are not really there. If this is for display only, then it doesn't matter.
The 2nd option is using a helper column ="AB"&A3 or using dynamic arrays ="AB"&A3:A9. In this case the new VALUE in the cell actually has the letters included. If you don't want the extra column you can copy and 'paste values only' on top of Col A and then delete that helper column.
The 1st option using Custom Number Format is quick & easy but has additional benefit/potential issue to note. Using this Custom Number Format will ONLY change how those cells are DISPLAYED. That means the VALUE in the cell is still the same. This is great if you might have other cells that you want to perform math on those values (e.g. =IF(A3+5>=A4, ... ) but if you want to use those cells in a lookup/comparison (e.g. =VLOOKUP(A3, ... ) you need to realize those letter prefix are not really there. If this is for display only, then it doesn't matter.
The 2nd option is using a helper column ="AB"&A3 or using dynamic arrays ="AB"&A3:A9. In this case the new VALUE in the cell actually has the letters included. If you don't want the extra column you can copy and 'paste values only' on top of Col A and then delete that helper column.