Text to Columns

About the Text to Columns

Split values into several columns — the job of Excel’s Text to Columns, without the wizard. Paste lines (a column copied from a spreadsheet, a list, a log) or open a CSV or Excel file and pick the column to split. Split at one or more delimiters (keeping quoted text such as "Rao, Asha" together), at fixed character positions, or by a regular expression whose groups become the columns.

Ready-made splits do the fiddly ones: full names into title, first, middle and last name and suffix; e-mail addresses into user and domain; URLs into their parts; dates into year, month, day and time; and addresses into street, city, state, postal code and country. Or go the other way and merge columns with a separator, skipping empty cells. Copy the result as tab-separated text and paste it straight into Excel or Google Sheets, or download a CSV or Excel file.

Common uses

• Splitting a “Full name” column into first and last names before an import or a mail merge.
• Separating city and postal code, or user and domain of e-mail addresses, for sorting and filtering.
• Breaking fixed-width records from an old system or a bank statement into fields.
• Combining first and last names, or address lines, into one column.

Delimiters, fixed widths and patterns

• Delimiters: any of the ticked characters (or your own, which may be longer than one character) splits a value. Text in quotes stays together, and a doubled quote inside it is one quote — as in a CSV file. Treat delimiters next to each other as one turns runs of spaces into one break. At most this many columns keeps the rest of the value in the last column.
• Fixed width: the numbers are the characters before each break, so 4, 6, 8 cuts 20251209 into 2025, 12 and 09. A ruler under the box shows where the breaks fall in the first lines.
• Pattern: each pair of parentheses in the regular expression is a column — ([A-Z]+)-(\d+) splits INV-0042 into INV and 0042; or split wherever the pattern matches. Lines that do not match are listed.

How the ready-made splits read values

• Names: titles in front (Mr, Mrs, Ms, Dr, Prof, Shri, Smt …) and suffixes at the end (Jr., Sr., III, PhD …) are taken off first; “Last, First” is read with the comma; the first word is the first name, the last word the last name (with particles such as van, de or bin), and anything between the middle name.
• E-mail addresses: user and domain at the last @.
• URLs: scheme, host, port, path, query and fragment; an address without a scheme (example.com/shop) is read as https.
• Dates: day/month/year, month/day/year or year/month/day as you choose (the order your country writes dates comes first), with month names (9 Dec 2025, Dec 9, 2025), a weekday in front (Tue, 9 Dec 2025) and times (2:05 pm becomes 14:05:00). Impossible dates such as 31/04 are listed, not guessed. Two-digit years follow Excel’s 2029 rule: 00–29 are 2000–2029, 30–99 are 1930–1999.
• Addresses: parts at commas or line breaks; a country name at the end goes to the country; a postal code goes to the postal code — at the end of the last part (PIN 411001, ZIP 94043 or 94043-1351, UK SW1A 2AA, Canadian M5H 2N2, Japanese 100-8111, Australian 2000) or in front of the city, as most of Europe writes it (10117 Berlin, 1012 LG Amsterdam, 00-950 Warszawa). A city with a short state code after it (Sydney NSW, São Paulo - SP) gives both. Otherwise the last remaining part is the state, the one before it the city, and the rest the street. Addresses written differently may need the delimiter split instead.

Limitations

• Up to 500,000 values at a time.
• The address and name splits follow the rules above; unusual formats (several names in one cell, addresses without commas) need checking.
• Merging takes the columns in the order you list them; to merge rows into one cell, use a list tool instead.

Privacy

Everything happens in your browser. What you enter or open here is not uploaded or stored by MySmartCoPilot.