CODEWITHSUNDEEP
excel
LIX
LIX

Reputation: 1529

Add common prefix to all cells in Excel

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
-----  >>>>  ----
1            X1
2            X2
3            X3

What is the easiest way to do this?

Upvotes: 49

Views: 549051

Answers (10)

Zabiullah V M
Zabiullah V M

Reputation: 19

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.

Upvotes: 0

Arun Dhiman
Arun Dhiman

Reputation: 1

Another way to do this:

  1. Put your prefix in one column say column A in excel
  2. Put the values to which you want to add prefix in another column say column B in excel
  3. In Column C, use this formula;

"C1=A1&B1"

  1. Copy all the values in column C and paste it again in the same selection but as values only.

Upvotes: 0

shaghayegh hamedani
shaghayegh hamedani

Reputation: 79

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"

Upvotes: 7

Vijay
Vijay

Reputation: 418

  1. Enter the function of = CONCATENATE("X",A1) in one cell other than A say D
  2. 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.

Upvotes: 3

Vaidya
Vaidya

Reputation: 1

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.

Upvotes: 0

Sudhi
Sudhi

Reputation: 11

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

Upvotes: 1

Shawon
Shawon

Reputation: 1030

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

Upvotes: 19

Shajith.m
Shajith.m

Reputation: 211

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

Upvotes: 21

Nilesh Deshmukh
Nilesh Deshmukh

Reputation: 359

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...

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169424

Type this in cell B1, and copy down...

="X"&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" & Trim(Str(.Cells(i, 1).Value))
    Next i
    End With
End Sub

Upvotes: 74

Related Questions