The fast way to Split URL to Columns using Google Sheets.

Need to split URL to columns in Google Sheets? As an ETL consultancy, we know the pains of having to “split text” to columns.

If I was going to automate splitting to rows, I would grab KNIME, alteryx, or use code/sql.

This is how I’d automate it, see the “Cell Splitter” tool, drag and drop, schedule and forget. I’m doing it right now, screenshot of KNIME below, it’s rather common to split content based on a condition, and can be accomplished with any programming language & time.

This ETL developer tool stuff can tend to be complex, so let’s discuss how we can do this utilizing Google Sheets. If you’re interested in learning more about knime, check out our website www.knime.dev!

Below we show how to split text to columns, how to automate getting coordinates and help you avoid this very boring task.

“Pick up a bean, put it in a bag, and then empty that bag in a pile over there. Now, start over.”

Here take this ax. You’re going to need it!

Split url to columns with this axe

Split URL to columns with this figurative ax. Watch your Fingers! We use Google Sheets because it’s a million times better than Excel.

This blog is the documentation built for our internal WIKI and cleaned up for consumption.

If you’re a technical expert, here’s all you need to know to split text to columns in google sheets.Split url to columns - Google Sheets shortcut key professor

  • Shortcut keys = ctrl+alt+d, E, arrow keys up and down select between the options, enter.

If you’re not as technical and prefer a broken out explanation with screenshots, read below.

If you want to be guided more and things explained, keep reading.

We explain how we get the URLs automated, and share the exact logic used to do it below. In our 11 step tutorial.

The use case is we have 100+ URLs with coordinates from a previous lesson.

Split URL to Columns in 11 easy steps.

Let’s use Google Sheets Split Url to Columns function now!

  1. Left-click the column header with URLs

    screenshot of URLs in column C on google sheet for demonstration to split to column

    Left click above –Headquarters, to select ‘C’ the column header that contains URLs to split.

  2. Left click Data, Ctrl+Alt+D or Ctrl+Option+D,
  3. Left click Split text to columns… or E

    split text to columns for with URL tutorial screenshot

    Split text to columns on Google Sheets is extremely powerful for manual manipulation of a table of data.

  4. On the bottom of your browser – you’ll see a subtle menu. If you have a big screen, you could miss this at first! Google Sheets natively uses comma separator.

    separator split to text drop down

    We bring our browser window into a smaller vertical box, otherwise this Separator: Comma drop-down may be hard to see at first. Left click Comma to activate the drop Split text to column Dropdown.

  5. Left click Custom to open the wild card Split text to column finder.
  6. Type @ to separate by a character or many.
  7. Copy – Latitude – and paste it in column D header.
  8. Left click column D to select the entire column of Latitude, Longitude, etc….

    screen shot of column header latitude for split url to columns tutorial

    Left-click the column header to select the entire column.

  9. Left click Data, Left click Split Text to columns… (Refer to Step 4 screenshot above)
  10. Use Custom for the separator, and leave it set to default comma.
  11. Copy –┬áLongitude – paste that to Column E.

    Latitude and Longitude have been split from a Google Maps Url copy paste.

Split URL to Columns in Google Sheets

Splitting text into columns is amazing if you want to avoid spending money to get coordinates.

Google Sheets offers a two-click win, 1 type win.

If you need any help with it, please comment below.

Saving time by macro-ing and using google sheets split text to columns function.

Stacking solutions are critical, we prefer KNIME, however this blog covers how to use Google sheets effectively.

We help some companies compute trillions of records using our Knime Consulting and Alteryx consulting expertise.

Enjoy!