You're human. You're smart, and when you look at the text Bob Tester, you quickly recognize that an individual with the first name Bob has the unfortunate surname of Tester. And when you see that Bob bought apple, carrot, banana, pig you know that he bought each item—not an unholy apple-carrot-banana-pig conglomerate.
Computers, however, take everything you tell them literally, so if an app has First Name and Last Name fields and you give it Bob Tester, it won't know what to do (or maybe it will put Bob Tester into the first field and give you an error because the last name field is empty). Nor will it know that Bob bought an apple unless it recognizes your comma-separated list as such.
Oftentimes, we'll need to work with data that needs to be split up like this, separating the first name and last name of a contact that's in an Excel column, for example, into separate columns. Or separating an address that's in one column into separate street address, city, state, and Zip Code columns or fields.
The good news is you can teach computers to be smarter. When you have text that needs to be split up, here's how to do it in a spreadsheet like Microsoft Excel and Google Sheets or automatically split text between your favorite apps with Zapier's Formatter tool.
How to Split Text in Microsoft Excel
The easiest way to split text is usually with a spreadsheet—and in Microsoft Excel on your Mac or Windows desktop, it only takes a few clicks.
Say you have a list of names like Bob Tester. If the names are already in a spreadsheet, just open the spreadsheet in Excel—or, if the list is somewhere else, perhaps in a document or website, just make a new spreadsheet in Excel, then copy and paste the text into the spreadsheet.
Select the column with the text you want to split. Click the Data tab in the top menu, and click Text to Columns—you should see it around the middle of the toolbar. That will open the split text dialog. The Delimited option should be checked by default—this specifies that the text you want to separate uses spaces, tabs, or other delimiters that you can choose how your data is split, so click Next. Now, choose what's separating your text. For our list of names, we'll check the box beside Space. If you have a list of comma separated values like the store list example above, check the Comma option instead.
Excel will then show you a preview of how the finished data will look, and will ask you if the split text is plain text or a date—and if the latter, you can set the correct date format. Click Finish, and you're done. Your data will be split into the columns you want.
Using Apple Numbers instead? Numbers doesn't have a built-in tool to split text, but on a Mac, you can use this Automator script to split text. Or, if you're using Microsoft Word, you can convert text to columns—select the text, then click the Insert tab, then click Table -> Convert. You can then separate text by spaces, commas, tabs, special characters, and even paragraphs.
How to Split Text in Google Sheets
There are even fewer steps to split text in one cell into multiple cells in Google Sheets. First import your spreadsheet into Google Sheets or make a new spreadsheet and paste in the data you want to split.
Select the text or column, then click the Data menu and select Split text to columns…. Google Sheets will open a small menu beside your text where you can select to split by comma, space, semicolon, period, or custom character. Select the delimiter your text uses, and Google Sheets will automatically split your text.
Automatically Split Text with Zapier Formatter
Spreadsheets are great for splitting large amounts of text, but they're not quite as quick when you just need to split text one time. Say you want to add your customers to your MailChimp email list—but your eCommerce app sends you their name as Bob Tester or Jane Doe, and MailChimp wants the name split into Bob and Tester with separate first and last names.
Normally you'd need to edit that text each time someone buys your product, and then add the new contacts to MailChimp one by one as they come in. App automation tool Zapier can speed things up by automatically copying the customer info from your store and then adding it to MailChimp. And its Formatter tool can split the text for you, too.
Here's how it works:
- Connect your trigger app—the app that has the text you need to split—to Zapier, and set up its trigger to copy the data you want.
- Add a Formatter step to your Zap. Select the Text action, then choose the Split Text transform.
- Click the
+button beside the Input field and select the field from your first app with the text that needs split. If your text is separated by spaces, you can leave the Separator field blank; otherwise, enter the character(s) that splits your text. - Finally, in the Segment Index field, choose if you want the First, Second, Last, or Second to Last item from your split text—to get the first name, say, you'd choose the First segment.
And that's it. Click Continue and test your Zap, and you'll get the plain split text you need. Add an Action app to your Zap, and you can now use that text in any other app—no manual editing needed.
Need more than one segment—perhaps the first and last name? Just add another Formatter step and select the correct segment to get the text you need.
Now, whenever you have data from one app that needs to be split before being added to another app, just use Zapier's Formatter to clean the text up for you. It's the quickest way to connect apps and send just the data you need to them.
Need more help? Here's a video on how to set up a Formatter action to split first and last names from a Facebook Lead Ad and add them to MailChimp:
Or, for a quick way to try out Zapier's Split Text formatter, use these pre-made Zap Templates to split text from Facebook Lead Ads and add it to MailChimp, or to add new customers to Help Scout—something the Craft Clubs team uses to speed up their customer support.
Whether you have a long list of text or just a simple name that needs to be split into individual columns, there's no reason to go through and hand-edit every line of text yourself. A spreadsheet or Zapier can do it for you almost instantly so you have more time to do your best work.
source https://zapier.com/blog/split-text-excel-zapier/
No comments:
Post a Comment