Inserting additional data into a cell

A

Alec H

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

Doug Kanter

Alec H said:
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?
Click to expand...

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

pinmaster

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
 
D

Dave Peterson

One more:

=if(a1="","","EEL-"&text(a1,"000"))

So leading 0's will show up:
EEL-001
 
Top