How do I prepend a string to a column of cells in Google Spreadsheets?

Question

I imagine this is laughably simple but I just don't know what to do. I have a column (A) of cells with strings in them and I want to simply prepend each string in every cell with a web address:

A
--------
1| /foo/bar/
2| /foo/foo/
3| /foo/baz/
...

I want:

A
--------
1| http://foo.com/foo/bar/
2| http://foo.com/foo/foo/
3| http://foo.com/foo/baz/
...

Answer 1

You can do that with a formula, so that e.g. column B will display the URL:

="http://foo.com" & A1

^{(the & is the string concatenation operator in Google Spreadsheets)}

Put this formula in B1, and copy-paste it to other cells in the B column.

Answer 2

For completeness, use this formula to add in B1:

=ARRAYFORMULA("http://foo.com" & A1:A)

See similar post here on Web applications: CONCATENATE a string with an ArrayFormula

Answer 3

You can put this in another cell: ="http://foo.com" & A1

This will result in:

  A            | B
----------------------------------------
1| /foo/bar/   | http://foo.com/foo/bar/
2| /foo/foo/   | http://foo.com/foo/foo/
3| /foo/baz/   | http://foo.com/foo/baz/

Or, if you prefer, you can use the CONCATENATE function:

=CONCATENATE("http://foo.com",A1)

(CONCAT is the same, but it only concatenates two values, while CONCATENATE lets you do multiple, even in ranges.)

Answer 4

You can do this using the search and replace feature in Google Sheets with regular expression and specified range.

In the search and replace dialog use these values

Find: ^/(.+) 
Replace: http://$1

Make sure you select the option "Search using regular expression" You can specify a cell range so you do not accidently replace values unintentionally.

The expression in the find field of ^/(.+) means the following:

• The ^/ indicates that the value being searched must start with /
• The () defines a capturing group so everything matched by the pattern inside the capture group will be able to be used in the replace field with $1
• The . means match any character.
• The + means match 1 or more.
• The combination of .+ means match 1 or more of any character.

The expression in the replace field of http://$1 means the following:

• The http:// is just text
• The $1 means first capture group
• Together the http://$1 means replace the cell with the text http:// plus whatever was matched in the first capture group.

Before Replace

After Replace