• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Extracting Text Strings Using Excel’s Text to Columns

You are here: Home / Excel / Extracting Text Strings Using Excel’s Text to Columns
Extracting Text Strings Using Excel’s Text to Columns
August 28, 2013 by Mynda Treacy

The Easy Way

In this example I’m going to show you how you can extract the domain name from a URL in Excel.

For example, taking the list of URL’s below we want the part in between the http:// or https://, and the next forward slash.

So the domain for the first URL below is www.ikea.com

List of URL's to extract Domain Name from

There are two approaches:

  1. The really complicated, banging head on desk, formula method or
  2. The click a few buttons Text to columns method.

I don’t know why but most people choose the formula option. Probably because they don’t know Text to Columns exists.

Don’t get me wrong. Formulas have their place (and I’ll cover them next week), but unless you’re setting up a template that you’re going to use over and over again, Text to Columns is your best friend.

Text to Columns

You’ll find the Text to Columns tool on the Data tab of the ribbon in the Data Tools section:

Excel Text to Columns

Clicking on the Text to Columns icon opens the Wizard in step 1 of 3:

Here you have to choose the file type that describes your data.

The rules are simple:

If you can draw a straight vertical line (that represents a column), between the data you want to separate, then you choose Fixed Width. If you can’t, then you need to choose Delimited.

You can see in the image below I’ve put a red line where I want the text separated, but the last row is going to leave a forward slash at the front of my domain.

Not to mention, the domains aren’t the same length so I can’t draw vertical lines after the .com/ part either.

Excel Text to Columns Wizard Step 1

So, in this case I need to choose the Delimited data type. Pressing ‘Next’ brings me to step 2 of the Wizard:

Excel Text to Columns Wizard Step 2

Here I can choose the delimiter I want to use to separate my data. Common delimiters are Tab, Semicolon, Comma (think .csv files) or Space, but I can also stipulate another delimiter that occurs in my data.

Since I want the data after the http:// I’m going to use the forward slash as my delimiter (also check the ‘Treat consecutive delimiters as one’ so that the // after the http: is not given its own column).

As soon as I enter the slash in the ‘Other’ field the Wizard gives me a preview of how my data will be separated.

Each set of data between a / is separated into its own column as indicated by the vertical lines in the Data preview section below:

Excel Text to Columns Wizard Step 2 set delimiters

In the next step I can choose which columns I want to keep (1) and in what format (2), and where to place the extracted data (3):

Excel Text to Columns Wizard Step 3

I only want to keep the column containing the domain so I’m going to select each column I don’t want by clicking in the column to select it (1), then in the ‘Column data format’ section (2) I’m going to select ‘Do not import column (skip)’.

You can see in the image below all of the column headers now read ‘Skip Column’ except the column containing the domain which I’m going to keep.

Excel Text to Columns Wizard Step 3 choose destination

Lastly I can choose the cell Destination for the domain names (3).

Tip: if you want to retain the original URL make sure you change the destination cell (3) to a cell in an empty column, otherwise it will replace your original data.

Finished Product

Here we have our domain names in under a minute, and without bruising to your head. 🙂

Excel Text to Columns finished product

Tip: Sometimes you need to run your data through Text to Columns in stages, extracting one part of the data in the first pass, then the next part, and so on. It all depends on the delimiters in your data.

Let’s take the data below as an example:

Separate text example

It’s almost in the right format to just use the Fixed Width format in step 1 of the Wizard, but we can see (image below) when I choose this option that it’s not quite right because the first row has 1 character too many in the second column:

Text to Columns Fixed Width

That’s ok, I can use the Delimiter method like this:

Text to Columns multiple delimiters

See in the image above how I’ve selected several delimiters based on what is present in my text.

I’ll need to run it through the Text to Columns twice though because I also want to separate the 01 from the preceding text and I can only choose one ‘Other’ delimiter at a time.

Other Uses for Text to Columns

Another great use for Text to Columns is fixing dates formatted as text.

Next week I’ll step you through the formula method. Don’t worry there’s no need to tape bubble wrap to your head in anticipation of banging your head on the desk.

I’m going to show you an easy way to build complex MID formulas without the usual frustration.

Extracting Text Strings Using Excel’s Text to Columns

More Text To Columns Posts

More Excel Posts

linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.


Category: ExcelTag: text to columns
Previous Post:Calculate Elapsed Days, Hours and Minutes in ExcelCalculate Elapsed Days, Hours and Minutes in Excel
Next Post:Extract Text from a Cell using MID FormulasExtract Text from a Cell using MID Formulas

Reader Interactions

Comments

  1. Vanessa Cuomo

    April 13, 2018 at 5:39 am

    Hello Again,
    How do I add the “do not import column” in power query while loading from a .csv file.
    Thanks

    Reply
    • Mynda Treacy

      April 13, 2018 at 9:28 am

      Hi Vanessa,

      Power Query doesn’t have the same dialog box as text to columns and the ‘do not import column’ is not an option. However, once you load the data to Power Query you can simply delete the columns you don’t want in your final data set.

      Mynda

      Reply
  2. Robert Wallis

    September 4, 2016 at 9:16 am

    Thanks for this tip for URLs. I think that most people choose the formula option because they don’t realize that Text To Columns exists.

    I’ve found a fast way to extract simple text strings into columns is by using an add-in like QuikBots by AXbean. It would be useful for those who can’t get the hang of column breaks.

    Reply
    • Catalin Bombea

      September 4, 2016 at 3:37 pm

      Thanks for sharing Robert.
      You will find that Power Query (which is a Microsoft add-in to excel 2010-2013, in excel 2016 is integrated as a standard tool, not an add-in anymore) has much more options to split into columns and to cleanup raw data. It’s a must know tool, it’s beyond any available add-ins on the market. And it’s free.
      Cheers,
      Catalin

      Reply
  3. Stephen Farrell

    August 28, 2013 at 11:16 pm

    I have found when I make a template where I am bringing in data from a text file, using the Text to Columns wizard is the way to go.
    When you save the file as an .xltx it will remember where your files reside and you never have to run the wizard again, it remembers all the steps you picked the first time. When you start your template (Recent Templates) all you have to do is double click on the file name in the folder and it imports it the way you wanted it.

    Reply
    • Mynda Treacy

      August 29, 2013 at 9:11 am

      Awesome tip, Stephen. Thanks for sharing it with us.

      Cheers,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.