Add common prefix to all cells in Excel

Question

I have a column with some text in each cell  I want to add some text  for example  X   at the start of all cells  For example   A             B -----   gt  gt  gt  gt   ---- 1            X1 2            X2 3            X3   What is the easiest way to do this

User · Accepted Answer

Type this in cell B1  and copy down       X  amp A1   This would also work    CONCATENATE  X  A1    And here s one of many ways to do this in VBA  Disclaimer  I don t code in VBA very often     Sub AddX       Dim i As Long      With ActiveSheet     For i   1 To  Range  A65536   End xlUp  Row Step 1          Cells i  2  Value    X   amp  Trim Str  Cells i  1  Value       Next i     End With End Sub

User · Answer

Michael   if its just for formatting then you can format the cell to append any value   Just right click and select Format Cell on the context menu  select custom and then specify type as you wish    for above example it would be X0  Here  X  is the prefix and 0 is the numeric after   Hope this helps    Cheers

User · Answer

Another way to do this    Put your prefix in one column say column A in excel Put the values to which you want to add prefix in another column say column B in excel In Column C  use this formula     C1 A1 amp B1    Copy all the values in column C and paste it again in the same selection but as values only

User · Answer

Enter the function of   CONCATENATE  X  A1   in one cell other than A say D  Click the Cell D1  and drag the fill handle  across the range that you want to fill All the cells should have been added the specific prefix text    You can see the changes made to the repective cells

User · Answer

Option 1  select the cell s   under formatting number custom formatting  type in   BOB  General   now  you have a prefix  BOB  next to numbers  dates  booleans  but not next to TEXTs  Option2   As before  but use the following format     BOB      now  you have a prefix BOB  this works even if the cell contained text  Cheers  Sudhi

User · Answer

Type a value in one cell  EX B4 CELL   For temporary use this formula in other cell  once done delete it    CONCAT XY B4    click and drag till the value you need  Copy the whole column and right click paste only values  second option     I tried and it s working as expected

User · Answer

Select the cell you want to be like this  go to cell properties  or CTRL 1  under Number tab in custom enter  X    Put a space between   and   if needed

User · Answer

Select the cell you want   Go To Format Cells  or CTRL 1    Select the  custom  Tab  enter your required format like    X    use a space if needed   for example  I needed to insert the word  Hours  beside my numbers and used this format      hours

User · Answer

Select the cell you want to be like this  Go To Cell Properties  or CTRL 1  under Number tab in custom enter  X

User · Answer

Go to Format Cells - Custom  Type the required format into the list first  To prefix  0  before the text characters in an Excel column  use the Format 0      Remember  use the character     equal to the maximum number of digits in a cell of that column  For e g   if there are 4 cells in a column with the entries - 123  333  5665  7 - use the formula 0      Reason - A single   refers to reference of just one digit

[excel] Add common prefix to all cells in Excel

Examples related to excel