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?

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
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, 2017 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, 2017 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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