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

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

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.




nmodafferi <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Originally Posted by nmodafferi

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)

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?
--
paul
remove nospam for email addy!



"nmodafferi" wrote:

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

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.
--
Gary's Student


"nmodafferi" wrote:

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

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.....
--
paul
remove nospam for email addy!



"nmodafferi" wrote:

> 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,
If your Numbers are there in Cell A1, If you want to Add letter" X" infront
of each number, you use formula
="X"&A1

"nmodafferi" wrote:

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

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




"nmodafferi" wrote:

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

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

"vishu" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "nmodafferi" wrote:
>
>> 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
>>
>>

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


"nmodafferi" wrote:

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

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



"nmodafferi" wrote:

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

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

"nmodafferi" wrote:

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

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

"nmodafferi" wrote:

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


"nmodafferi" wrote:

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

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





"nmodafferi" wrote:

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