3

I have a spreadsheet in Google Sheets; one column tracks hashtags that were used in blog posts on a website. The posts are written on the website, and then the hash tags are copied from the website and pasted onto the sheet.

The problem is that the hash tags are formatted on the website in a strange way (they cannot be input as plain text because the designer hated making things easy), so when they are copied to the clipboard, they have no spaces like this:

#sex#drugs#rocknroll

However, I need to format them with a space in front of the #:

#sex #drugs #rocknroll

Right now I'm manually inserting the spaces, but sometimes there are 20 hash tags per post, so that will be painful long-term. Is there a way to automatically insert the space in Google sheets, or using Apps Script?

4

It seems what you want is a very simple Replace # with # (hash with space hash).

If to skip a space at the start an alternative is the following formula:

=trim(substitute(A1,"#"," #"))

An alternative for skipping a space at the start, without an additional column, is to use a regular expression in Find/Replace:

Find: (.)(#)
Replace: $1 $2

  • This works, but then I think I need two columns instead of one-- Column A to paste the text from the clipboard, and Column B to do the Replace. Is there a way to do this in a single column? – Patrick Kenny May 26 '17 at 13:14
  • Ok. In that case, I'll make a separate sheet, add the formula to that sheet, and then paste the tags in and afterwards copy the reformatted tags to the original sheet. This is faster than running a find-and-replace, and I have to do this every day. – Patrick Kenny May 26 '17 at 13:22

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.