Adding letters to an existing column of numbers

[Katstorm]

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.

[#2]

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

[#3]

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.

[#4]

Appreciate the info, it worked with a slight modification. As @mtarler had mentioned your 1st suggestion works if I don't need the underlying code and just need the visual. However as these numbers are from product code and I would be uploading to a database, I need it to read the whole thing. I took a short cut, I used your suggestion to format the range of cells using the "AB"####, then copied and pasted into OneNote, then copied and pasted back and it worked. Thanks for the hand up, it's appreciated.

[#5]

@Katstorm 

you are welcome, glad to help