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 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 |
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 |
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 |
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 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 |
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" 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 |
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 |
All times are GMT +1. The time now is 08:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com