Prepend cells with certain text in Excel

3

2

I want to take some of my cells that look like this:

PETROLIA ONTARIO CANADA NON1RO,              
VANCOUVER BC V5L3B1,              
ONTARIO CANADA LOR2C0,              
DN58BY ENGLAND,              
DN58BY ENGLAND,

and change them into this:

(intl) PETROLIA ONTARIO CANADA NON1RO,              
(intl) VANCOUVER BC V5L3B1,              
(intl) ONTARIO CANADA LOR2C0,              
(intl) DN58BY ENGLAND,              
(intl) DN58BY ENGLAND,

Is there an easy way to do that?

Jason Swett

Posted 2010-11-10T19:19:31.047

Reputation: 319

Answers

5

use the CONCATENATE formula. the below example assumes your first value is in cell A1.

=CONCATENATE("(intl) ", A1)

Xantec

Posted 2010-11-10T19:19:31.047

Reputation: 2 303

How do I copy and paste the raw data instead of copying and pasting something that's dependent on a formula? If I copy and paste that stuff into the original column, I get recursion. – Jason Swett – 2010-11-11T15:51:52.473

when you Copy and Paste, make sure you paste the values only. This can be accomplished by Right-Click > Paste Special > Values – Xantec – 2010-11-11T16:09:21.133

2

I think using the concatenate function requires unnecessary typing. Just use the ampersand sign to concatenate values:

="(intl) " & A1

I think that's easier.

Sux2Lose

Posted 2010-11-10T19:19:31.047

Reputation: 2 962

1

If the above data is in cells A1-A5, you can get the desired result if you put the following in cell B1:

=CONCATENATE("(intl) ", A1)

and copy-paste it to B2-B5

icyrock.com

Posted 2010-11-10T19:19:31.047

Reputation: 4 623

Dupe. – Mehper C. Palavuzlar – 2010-11-10T21:10:51.857

0

You can copy the following into the first column of the next and drag the bottom left corner of that cell down:

=CONCATENATE("(intl) ", INDIRECT("RC[-1]",0))

Soosh

Posted 2010-11-10T19:19:31.047

Reputation: 584

Asked: 2010-11-10T19:19:31.047

Viewed: 14 933 times

Active: 2017-05-30T10:22:49.907