Comma Separated String To List Excel For Mac

In “,” we showed you how to extract substrings from the left, right, or middle of a string with a fixed number of characters. We followed up that tip with “,” which lets you extract substrings from strings of varying lengths. In this article, we’ll show you the fastest and easiest way to parse a string into separate columns. The best part is, this technique doesn’t require any formulas! If there’s a delimiter, Excel can parse the text Recently I noticed that a coworker was editing a spreadsheet that contained entries like the ones shown in Figure A. She had been given this raw data and asked to total and subtotal the faxes and e-mails.

Convert column of data to comma separated list of data instantly using this free online tool. Contact Us If you happen to have any comments, suggestions or feedback. And now on to the How-to Solve This Problem with Microsoft Excel Step 1. Copy the comma delimited text into your clipboard from your text editor or Microsoft Word. Fire up MS Excel and paste the comma separated text into a cell. Click on the Data Tab and then select Text to Columns. Now select Delimited as that is the type of Data we are working with.

Unfortunately, the numbers and the labels were combined into a single string. This poor soul was manually rekeying the number and the labels, and she had 700 rows of data. Install my passport for mac.

Figure A We’ll show you the easy way to separate the numbers from the labels in this raw data. Fortunately, I was able to come to my coworker’s rescue by showing her how to use Excel’s Text To Columns feature to parse that text automatically.

Here’s how it works. First, select the column of cells that contains the raw data, then open the Data menu and choose Text To Columns. When you do, Excel launches the Convert Text To Columns Wizard. Make sure the Delimited radio button is selected, and click Next. Since the delimiter in this raw data is simply the space between the number and the label, activate the check box for Space, as shown in Figure B.

(Deselect Tab, which is the default selection.). Figure C With just a few mouse clicks, you can convert a column of labels into columns of data. A string by any other name This tip comes in handy when you have a column of names in the format First Last and you want to separate out the last names—just designate the space as the delimiter. The Text To Columns tool will “cut” those strings at any delimiter you specify, so you and your users need never again rekey data into separate columns. Tales of Excel-lence To comment on this tip (or to share your favorite Excel trick), please post a comment below. Each week, Jeff Davis tells it like he sees it from the trenches of the IT battlefield.

And you can get his report from the frontlines delivered straight to your e-mail front door. Subscribe to Jeff's, and you'll get a bonus of Jeff's picks for the best Web stuff, exclusively for TechMail subscribers. Related Topics.