10

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/
...
13

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.

| improve this answer | |
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.)

| improve this answer | |
5

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

| improve this answer | |
1

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

| improve this answer | |
New contributor
Luke C is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.