1

A company sells a lot of carrots; every Carrot has a unique product ID.

I want the first row, containing a 9 digit product-ID to make every ID into a clickable URL format, containing a treated string corresponding to the product-ID.

Here is an example of a given URL:

https://example.com/wp-admin/edit.php?action2=-1&action=-1&fb_sync_enabled&paged=1&post_status=all&post_type=product&product_type&s=010666666&stock_status

The ID's are made out of 9 digits, separated past the fourth one by a hyphen.

In order to create a functioning URL the hyphen needs to be removed.

For the above mentioned example, the product ID with that very clickable link would be 01066-6666 .

A 1 is a title and should thus be ignored.

Any method that renders this result is the right one.

3
  • 2
    Welcome. An interesting scenario - but is there a question? – Tedinoz Apr 28 '20 at 10:39
  • Yes. There is. How does one achieve the results illustrated in the example? – dotMavriQ May 2 '20 at 8:47
  • 1
    Use substitute to remove the dash; then use & the string concatenation operator to combine the first half of the url with the last half of the url; wrap the whole thing in hyperlink. Copy the formula down as many rows as you need. – Tedinoz May 2 '20 at 9:27
0

I cannot answer how to solve this issue within Google sheets. But since the original Question says this:

Any method that renders this result is the right one.

here is how I would solve the problem with a workaround:

It requires the following conditions to be met:

  • From your Question I understand that all URLs are in the same column (for easy copying)
  • the URLs look the same / have the same length
  • You have a code editor installed that allows mass-editing, for example VS Code. you could probably substitute this for a online version

Follow these steps:

  1. open new VS Code file
  2. copy the whole column of your sheet into VS Code
  3. delete the line that you want to have ignored (A1)
  4. on line 1, click exactly on the position where the hyphen needs to be inserted
  5. hold CTRL + ALT on your keyboard
  6. press the arrow down key on your keyboard until you hit the last line
  7. you will notice the cursor duplicated itself and shows on each line now, exactly at the position where you need to insert the hyphen
  8. insert only 1 hyphen
  9. the hyphen will get inserted into every line at the position of the respective cursor in that line
  10. copy all URLs
  11. paste them back into the sheet and overwrite the previous values

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.