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

CodePudding user response：

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:

• 

  ---

  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