Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Excel question

quigleybc

quigleybc

macrumors 68030
Original poster
Jun 17, 2005
2,966
0
Beautiful Vancouver British Columbia, Canada
Hi,

I have a giant column in Excel.

Each cell in the column contains three words of text.

Example:

Soft Shell Jacket.

I need to simply add the word F06 to the end of every cell of text to make it read:

Soft Shell Jacket F06

Is there a way to add that text to the entire column all at once? Instead of entering it one by one?

The three words will change after every tenth row or so, so I can't just copy one and paste to the whole column..and if I go, Edit>replace> 'space' with 'F06'

I get SoftF06ShellF06Jacket

and that won't work.....

Any suggestions?

thanks in advance

:)
 
It may not be the most efficient, but I would use the concatenate function.

If you insert two blank columns next to the existing column with text, then copy F06 all the way down the first blank column. Insert the formula =CONCATENATE(first cell, second cell) in the second blank column and copy all the way down, it should add F06 to each cell. You can then use copy, paste special, values to get rid of the formulas.

Like I said, probably not the most efficient, but it'll do the job fairly quickly.
 
say the column that has "Soft Shell Jacket" is column A, start with cell A1

then in B1 (or wherever), type =A1&" F06", then drag down...

oh, do you have the original as "Soft Shell Jacket" or "Soft Shell Jacket." (the period)?
 
bearbo said:
say the column that has "Soft Shell Jacket" is column A, start with cell A1

then in B1 (or wherever), type =A1&" F06", then drag down...

oh, do you have the original as "Soft Shell Jacket" or "Soft Shell Jacket." (the period)?
Click to expand...

That's neat...I'm not a power-Excel user, so I haven't discovered tricks like this yet. One question though...this generates a second column with the desired content it. But deleting the original column of course destroys the new content because it's just a formula. This can be remedied by using "Paste Special" to copy and paste just the content back into the original column, allowing you to delete the "formula" column. It's not difficult, but is there an even easier way to do that?
 
WildCowboy said:
That's neat...I'm not a power-Excel user, so I haven't discovered tricks like this yet. One question though...this generates a second column with the desired content it. But deleting the original column of course destroys the new content because it's just a formula. This can be remedied by using "Paste Special" to copy and paste just the content back into the original column, allowing you to delete the "formula" column. It's not difficult, but is there an even easier way to do that?
Click to expand...
There's not really an *easier* way to do it. The suggestion of using '=A1&" F06"', filling it down, and then copying it/paste specialing it as values is the quickest solution. You could also write a macro to go down through the column and append " F06" to every cell, but that would take longer to do.
 
clayj said:
There's not really an *easier* way to do it. The suggestion of using '=A1&" F06"', filling it down, and then copying it/paste specialing it as values is the quickest solution. You could also write a macro to go down through the column and append " F06" to every cell, but that would take longer to do.
Click to expand...

Right, I thought of a macro, but I agree that this is simpler...thanks.

I guess the only way that would be simpler is if you could easily convert the formula entries directly to content within their own column and then delete the original column, saving you the step of "paste specialing."
 
WildCowboy said:
Right, I thought of a macro, but I agree that this is simpler...thanks.

I guess the only way that would be simpler is if you could easily convert the formula entries directly to content within their own column and then delete the original column, saving you the step of "paste specialing."
Click to expand...
Well, if you found yourself doing this sort of thing quite frequently, then you'd want to write a macro (callable from a menu item... think "Tools > Append") that would prompt you for a value to be appended to all currently selected cells. Then you could just select the range, hit Tools > Append, type " F06", and click OK.
 
quigleybc said:
Hi,

I have a giant column in Excel.

Each cell in the column contains three words of text.

Example:

Soft Shell Jacket.

I need to simply add the word F06 to the end of every cell of text to make it read:

Soft Shell Jacket F06

Is there a way to add that text to the entire column all at once? Instead of entering it one by one?

The three words will change after every tenth row or so, so I can't just copy one and paste to the whole column..and if I go, Edit>replace> 'space' with 'F06'

I get SoftF06ShellF06Jacket

and that won't work.....

Any suggestions?

thanks in advance

:)
Click to expand...

sounds like you already have a solution, but you could also do a find & replace: Find "Soft Shell Jacket" and Replace with "Soft Shell Jacket F06". approach it as replacing the whole thing vs. adding something to the end...
 
r6girl said:
sounds like you already have a solution, but you could also do a find & replace: Find "Soft Shell Jacket" and Replace with "Soft Shell Jacket F06". approach it as replacing the whole thing vs. adding something to the end...
Click to expand...

Yeah, that was the issue he was talking about...not all of the entries he needs changed are "Soft Shell Jacket."
 
r6girl said:
sounds like you already have a solution, but you could also do a find & replace: Find "Soft Shell Jacket" and Replace with "Soft Shell Jacket F06". approach it as replacing the whole thing vs. adding something to the end...
Click to expand...
He said that the column was filled with all sorts of different values, so a Find and Replace would not work.
 
clayj said:
He said that the column was filled with all sorts of different values, so a Find and Replace would not work.
Click to expand...

ah, i misread. thought he was just looking for specific entries in the column, not the whole column...
 
quigleybc said:
When I go to "paste special"

Should I choose "all"?

or "formula"

?

it gives me a few options,

and should I paste it into the original column? or can it be pasted into the column with the formula, or even a new blank column?

thanks!
Click to expand...
Paste VALUES. That will eliminate the formula while leaving the result of the formula in place.

And you can paste it in either place, so you might as well paste it on top of the original values. Just make sure the result of the formula is correct before you do this.
 
clayj said:
Paste VALUES. That will eliminate the formula while leaving the result of the formula in place.

And you can paste it in either place, so you might as well paste it on top of the original values. Just make sure the result of the formula is correct before you do this.
Click to expand...


You rock, Values worked perfectly!!

Thank you guys soo much! saves me a ton O' work today! woo hoo...!!

more time for MR

and clay....what happened to Grisham?

:D :D :D
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back