-1

I want to cut out substrings from this url XY.com/de/haus/dach-ziegel-stein/ and put the values each in its own columns in Google Spreadsheet.
I want to cut out by / and by -.

With this url example:

  • Column A should be de
  • Column B should be haus
  • Column C should be dach
  • Column D should be ziegel
  • Column E should be stein
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
johny
  • 159
  • 1
  • 13

2 Answers2

1

You can use the following single formula for a range

=INDEX(IFERROR(SPLIT(
                 REGEXREPLACE(
                    REGEXREPLACE(A125:A128,"^\w+\.\w+\/"," "),
                         "\/|\-"," ")," ")))

(do adjust ranges and locale according to your needs)

Or simpler

=INDEX(IFERROR(SPLIT(
     REGEXREPLACE(A125:A128,"\w+\.\w+\/"," "),"/|-")))

Functions used:

marikamitsos
  • 10,264
  • 20
  • 26
0

You can use a split formula like so to split by multiple delimiters:

=SPLIT(REGEXREPLACE(right(A1,len(A1)-(find(".com",A1)+3)), "-|/", ""), "")

ex

This formula works by first trimming off the first part of the URL:

right(A1,len(A1)-(find(".com",A1)+3)

It finds where the end of the url, .com, starts within the string, and then adds 3 to get the end of the url (ie in the example URL, XY.com/..., .com starts at position 3, so we add 3 to compensate for the c o m, which gets us an ending position of 6). From there, we trim the URL starting on the right hand side, getting the length of the entire URL minus the ending position of the .com.

Finally, we replace the two delimiters that we want to split by with a unicode character, in this case .
REGEXREPLACE(right(A1,len(A1)-(find(".com",A1)+3)), "-|/", "")
And then simply split that string by the unicode character, effectively splitting by both - and /.

kaitlynmm569
  • 1,605
  • 1
  • 6
  • 18
  • `=SPLIT(REGEXREPLACE(REGEXEXTRACT(A1,".com\/.*"),".com/",""),"/-")` – W.S. Jul 15 '22 at 13:35
  • Ok thanks: What is the different about your solution and just that: =SPLIT(D8;"/-") it works like the same or not? – johny Jul 15 '22 at 14:00
  • @johny The only real difference is that my equation removes the beginning of the URL. Mine was more complicated mainly because I didn't realize I was able to define multiple delimiters, guess you learn something new every day! – kaitlynmm569 Jul 15 '22 at 14:03