how to add a letter in front of each number in a cell

[Guest]

I am working on this project & I cant figure out how to add a letter in
front of each number in a cell without clicking in to each individual cell
and typing it in. (very time consuming and tedious) The problem is - it's not
all the cells but a majority, all of the numbers are in the same column. To
explain, these are documents in storage. Each item is assigned a number (not
in any particular numerical order but they have to stay in the order they are.

Example:

X39655
X39656
X39711
39662
39664
39665

The last three numbers need the X in front of them.


Any ideas? Thanks so much!!


I read this previous reply and I'm not getting it.......I'm Excel challenged
:-(

Use a help formula

=Sheet1!A1&"scc"

copy down/across, then copy and paste special as values in place ,
finally replace the old values with the new

Regards,

Peo Sjoblom

 

[R.VENKATARAMAN]

suppose your data are in A1 t0 A6

in B4 type
="x"&A4
you can copy this down to b5 abd B6
try and see whether you get what you want.

 

[greg7468]

Hi,
depending on what sort of data you need to put in front of the numbers,
but two ways I can think of

say your data starts in A1

in column B go down and put the data in the cells where you want to add
something before the corresponding cell in column A.

so your data would look like this.

- col A ----- col B ----- col C
X39655
X39656
X39711
39662--------x
39664--------x
39665--------x


in C1 put the formula =B1&A1

you can then copy this down the column (click on the black square at
the bottom right corner of C1 and drag it down the column C.)

If you have nothing but X's to put at the start you could sort the data
numerically so that all the X numbers were at the bottom, then in B1 put
the formula ="X"&A1

again copy this down until you reach the numbers prefixed with the
"X".
(if you choose the second one befroe you start you may want to insert a
helper column with a number series in say 1-1000 so after sorting and
inserting the X's you can resort back to the original list.)

HTH.

 

[swchee]

I am working on this project & I cant figure out how to add a letter in
front of each number in a cell without clicking in to each individual
cell
and typing it in. (very time consuming and tedious) The problem is -
it's not
all the cells but a majority, all of the numbers are in the same
column. To
explain, these are documents in storage. Each item is assigned a number
(not
in any particular numerical order but they have to stay in the order
they are.

Example:

X39655
X39656
X39711
39662
39664
39665

The last three numbers need the X in front of them.


Any ideas? Thanks so much!!


I read this previous reply and I'm not getting it.......I'm Excel
challenged
:-(

Use a help formula

=Sheet1!A1&"scc"

copy down/across, then copy and paste special as values in place ,
finally replace the old values with the new

Regards,

Peo Sjoblom

Hi, you may try this, let say the original data found in column A1. add
the below formula in colum B1 and drag down . Good luck


=IF(LEFT(A1,1) <> "X", "X"&A1, A1)

 

[Guest]

perhaps repeating some info,but are you saying that some numbers already have
the x in front and some dont,no particular order or pattern but all prefixes
if present are x and if no x is present then an x must be prefixed?

 

[Guest]

use =IF(LEFT(A1)="x",A1,"x"&A1) in an adjacent cell, then copy down. if the
original cell already starts with an x, none is put there.

 

[Guest]

ok if i read you correctly you need to put this formula in a column(say b1)
next to your column of numbers
=IF(LEFT(A1,1)="x",A1,CONCATENATE("x",A1)).This example assumes that your
column of number starts in A1.Now copy this formula down column b to bottom
of your data.You should now have a column all prefixed with x.Highlight the
whole column of new numbers and copy, go back to your original column (say A)
and paste special>values,and lastly delete your column of formulas.....

 

[Guest]

Hi,
If your Numbers are there in Cell A1, If you want to Add letter" X" infront
of each number, you use formula
="X"&A1

 

[Guest]

This should sort it:

If you have your numbers in say column E (just numbers - no x)
Then put an x in Column E next to numbers that don't have x's in E

Then in column F write a formula that says:

=LEFT(D8,1)&E8

this will join the 2 columns up. The nuber 1 after D8 just says take the
1st number. If you had e.g. the name John in the column and your sais 2... it
would take the first 2 letters i.e JO

hope this helps

 

[N Harkawat]

=if(isnumber(a1),"X"&A1,A1)

Hi,
If your Numbers are there in Cell A1, If you want to Add letter" X"
infront
of each number, you use formula
="X"&A1

 

[Guest]

if X is the character which needs to go in front of all data without an X

use a helper column ( a column which you can delete later with out losing
any current data)
for example if your cells are in column A I wuld insert a column B.
in B1 (or whatever row has the initial data) enter
=if(left(A1,1)="X",A1,"X"&A1)
copy this down to the bottom of your data.
copy these cells and paste special values over the corresponding Cells in
Column A.
Now delete Column B

 

[Guest]

Try using the formula = LEFT(D3,1)&E3 for example.
U will have to put an in the column next to your data numbers

this formula will then combine an x with your number

 

[Guest]

this may be a second response
Insert a new column B
in B1
=If(left(A1,1)="X",A1,"X"&A1)
copy to the bottom of the data
Copy the data in column B and paste special values over Column A
Delete Column B

 

[Guest]

Assuming that you want to add X as the first character of every one, you can
use this formula where A1 contains the numeric (39662).

="X"&A1

If the first character resides in B1, you could change it to

=B1&A1

 

[Guest]

Hi,
I believe that the SAME letter ("X") has to go in front of those numbers.
Let's imagine that Column A contains the data, starting with Row 2.
Create a dummy column in any empty column (let's say Col B) with the
following formula:

In cell B2 enter the formula,
=IF(LEFT(A2,1)="X",A2,"X"&A2),
and fill-down Col B down to its last row.
Col B should have the modified data.
Now select the Col B, click Copy, then click Edit-Paste Special-Values.
If you want, you can now delete the original "flawed" column (i., Col A) and
move the modfied values in Col B to Col A.

Regards.
B.R.Ramachandran

 

[Guest]

First, save your data and use a copy for this exercize.....

Assuming your data is in column A, and column B is blank....in B1 put this
formula and copy down as far as you have data in column A......

=IF(LEFT(A1,1)="X",A1,"X"&A1)

You can then highlight the column B and do Copy > PasteSpecial > Values to
get rid of the formulas.........then, you can delete your original column A
if you wish.

Vaya con Dios,
Chuck, CABGx3