Merge/Combine different data types into one cell?

**sjleiben** · 10-10-2012, 01:18 PM

Hi everyone. I'm using 2007 Excel (also have access to 2003).

My goal is to add alpha characters to columns of numerical data. Basically, I have a report of employees (about 1300) that has their ID numbers (i.e., 98765). I want to add two alpha characters (i.e., AB) in front of the ID numbers without having to go into each cell and manually typing or pasting the letters.

Here's a quick example:
Capture.PNG

I tried to merge the cells, but received the message about only displaying the data in the top-left cell. I searched the forums and found a lot of answers, but they all reference data of the same type (i.e., all letters such as first and last names).

Also, I'm not looking to keep the original cells. The file will be saved as a CSV and uploaded into a program, so I cant' have extra data floating around in the sheet.

Any help would be greatly appreciated.

**dwint** · 10-10-2012, 01:21 PM

say that "AB" in cell A1, ID number in cell B1

in c1 = A1&B1

copy down

**FDibbins** · 10-10-2012, 01:22 PM

you could use a helper column. assume your ID number is in column A...

="AB"&A1
copied down. you can then copy/paste values either over the formula, or over the original data...then delete which-ever column you dont need to keep

**sjleiben** · 10-10-2012, 01:46 PM

@dwint - Since I can't have the extra cells after combining the data, would the C1 cell give an error when I remove cells A1 and B1?

@FDibbins - I'd consider myself a noob when it comes to the macro and coding side of Excel, so sorry if I'm not understanding. Wouldn't this be just as much if not more work than just typing in the AB for each cell? If I have 1300 unique numbers in, lets say, cells B2 thru B1302 it seems like a lot of extra formatting? Am I not understanding your idea?

**dwint** · 10-10-2012, 01:52 PM

Originally Posted by sjleiben

@dwint - Since I can't have the extra cells after combining the data, would the C1 cell give an error when I remove cells A1 and B1?

before you remove cell A1 & B1, please copy cell C1 and paste as value
so, when you remove cell a1 & b1 it would not give error in c1

**sjleiben** · 10-10-2012, 02:10 PM

Thanks dwint! I hit a roadblock when trying to copy/paste the cells since the formula was the thing being pasted. A coworker said to try the Paste Special - Value and it worked.

Thanks again to both you and FDibbins for the help!!!