14

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/
...
pnuts
  • 17,883
  • 5
  • 55
  • 103
Timmy O'Mahony
  • 243
  • 1
  • 2
  • 6

4 Answers4

15

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.

Vidar S. Ramdal
  • 17,409
  • 11
  • 71
  • 82
6

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

Community
  • 1
Jacob Jan
  • 23,322
  • 16
  • 106
  • 195
5

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

Before Replace

After Replace

After Replace

marikamitsos
  • 13,110
  • 4
  • 35
  • 59
Luke C
  • 51
  • 1
  • 2
5

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

ale
  • 52,972
  • 42
  • 165
  • 314