Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
nmodafferi
 
Posts: n/a
Default 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


  #2   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

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 wrote in message
...
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




  #3   Report Post  
greg7468
 
Posts: n/a
Default


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.


--
greg7468


------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=379578

  #4   Report Post  
swchee
 
Posts: n/a
Default


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


--
swchee
------------------------------------------------------------------------
swchee's Profile: http://www.excelforum.com/member.php...o&userid=24279
View this thread: http://www.excelforum.com/showthread...hreadid=379578

  #5   Report Post  
paul
 
Posts: n/a
Default

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




  #6   Report Post  
Gary's Student
 
Posts: n/a
Default

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


  #7   Report Post  
paul
 
Posts: n/a
Default

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


  #8   Report Post  
vishu
 
Posts: n/a
Default

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


  #9   Report Post  
Phil Wales
 
Posts: n/a
Default

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


  #10   Report Post  
N Harkawat
 
Posts: n/a
Default

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

"vishu" wrote in message
...
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






  #11   Report Post  
bj
 
Posts: n/a
Default

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


  #12   Report Post  
Phil Wales
 
Posts: n/a
Default

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


  #13   Report Post  
bj
 
Posts: n/a
Default

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


  #14   Report Post  
Barb R.
 
Posts: n/a
Default

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


  #15   Report Post  
B. R.Ramachandran
 
Posts: n/a
Default

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




  #16   Report Post  
CLR
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Get Column Letter from Column Number Gary Brown Excel Worksheet Functions 7 June 11th 05 02:08 AM
column header changed from letter to number, how return to letter Ron Excel Discussion (Misc queries) 2 May 9th 05 08:34 PM
How do I add row total when cell contains both number and letter . cbarcroft Excel Discussion (Misc queries) 4 April 18th 05 11:52 PM
How do I get a letter in one cell ito equal a number in anotherl.. Dave Cadey Excel Discussion (Misc queries) 4 February 25th 05 03:37 PM
Adding + in front of a number Ted Metro Excel Discussion (Misc queries) 3 December 29th 04 11:45 PM


All times are GMT +1. The time now is 12:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"