Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting additional data into a cell
Hi, I have a column of data that consists of 2 and 3 digit numbers (eg 215 or 56), I need to add a prefix of EEL to all of them. As there are over 400 of them, they are all different and they are not concurrent (some numbers are missing) I cant just drag and fill..... Any ideas? -- Alec H ------------------------------------------------------------------------ Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042 View this thread: http://www.excelforum.com/showthread...hreadid=510610 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting additional data into a cell
"Alec H" wrote in
message ... Hi, I have a column of data that consists of 2 and 3 digit numbers (eg 215 or 56), I need to add a prefix of EEL to all of them. As there are over 400 of them, they are all different and they are not concurrent (some numbers are missing) I cant just drag and fill..... Any ideas? Let's say you put this formula in cell B1: =CONCATENATE("EEL-",A1) If cell A1 contained 215, then cell B1 (containing the formula) would now contain: EEL-215 Now, you need to copy the value from B1 to A1. Edit, Paste Special, Values, and click OK. This pastes just the value, but not the formula, from B1 to A1. At this point, you're probably thinking what a pain it'll be to type that formula all the way down column B, but you don't have to. With the cursor in B1, Edit, Copy (or CTRL-C). Now, select all the cells you want to contain the formula, and then Edit, Paste. Excel will automatically change the A1 reference in the formula to A2, A3, etc, depending on line number. By the way, the formula does NOT have to be in an adjacent column. It could be way off to the right where it's not even on the screen. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting additional data into a cell
Try this: use or insert a blank column, next....say that your data is in A1 then in the blank column type =IF(ISNUMBER(A1),"EEL"&A1,"") copy down as far as needed, next copy the new column and paste over the original column ...Edit/Paste Special and Values. Clean up or delete the new column when done. Hope this helps! Jean-Guy -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=510610 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting additional data into a cell
=IF(A10,"EEL"&A1,"") and copy down............
Vaya con Dios, Chuck, CABGx3 "Alec H" wrote: Hi, I have a column of data that consists of 2 and 3 digit numbers (eg 215 or 56), I need to add a prefix of EEL to all of them. As there are over 400 of them, they are all different and they are not concurrent (some numbers are missing) I cant just drag and fill..... Any ideas? -- Alec H ------------------------------------------------------------------------ Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042 View this thread: http://www.excelforum.com/showthread...hreadid=510610 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting additional data into a cell
One mo
=if(a1="","","EEL-"&text(a1,"000")) So leading 0's will show up: EEL-001 Alec H wrote: Hi, I have a column of data that consists of 2 and 3 digit numbers (eg 215 or 56), I need to add a prefix of EEL to all of them. As there are over 400 of them, they are all different and they are not concurrent (some numbers are missing) I cant just drag and fill..... Any ideas? -- Alec H ------------------------------------------------------------------------ Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042 View this thread: http://www.excelforum.com/showthread...hreadid=510610 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Last cell of data in a column | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Inserting additional data into an existing cell? | Excel Discussion (Misc queries) | |||
Multiple worksheet queries | Excel Worksheet Functions |