Access Text to Columns: Split Data in a Field

Microsoft Access does not have an existing-table command named Text to Columns like Excel does. To split a delimited text file as you bring it into Access, use the text import wizard; to separate values already stored in one field, use a select query, an update process, or VBA, depending on whether you need a one-time result or a repeatable solution.

What Text to Columns means in Access

Text to Columns is Excel’s name for dividing the contents of one cell into multiple columns using a delimiter, such as a comma, tab, or space. Access is a relational database, so its comparable task is to turn one text value into separate fields, either during import or as part of a query or data-cleaning process. The tools and the best approach depend on where the data currently lives.

For example, a field might contain Chen, Maya. You may want to store Chen in a LastName field and Maya in a FirstName field. If the source is a CSV file with those values already separated by a comma, Access can import them as separate fields. If the whole name is already in one Access field, you need to parse it.

Before splitting anything, decide whether the resulting pieces should become permanent data in a table or simply appear as separate columns in a report or query. A query can display calculated pieces without changing the stored record. That is often safer while you check whether the split rules handle all the real-world values.

Split a delimited text file during import

If the source is a plain-text file, CSV, or tab-delimited file, use Access’s import wizard. In Access for Microsoft 365 on Windows, choose External Data > New Data Source > From File > Text File, select the file, and choose whether to import the source into a new table, append its records to an existing table, or link to the file. The available choices depend on what you want to do with the source.

For a delimited file, select the Delimited option in the wizard, then select the delimiter used in the file, such as comma or tab. Check the preview to confirm that values appear in the intended columns. If the first row contains field names, identify it as a header row. The wizard then lets you review field names and data types before completing the import.

Open the Text File import option and select the source file.
Choose the appropriate import, append, or link option.
Select Delimited and specify the actual separator used by the file.
Inspect the preview, including rows with quoted text or missing values.
Set field names and review data types before finishing the wizard.

A comma-separated file can contain commas inside a value, such as Doe, Inc.. Well-formed CSV files usually surround such a value with quotation marks so the comma is treated as part of the value rather than a field boundary. Check the preview rather than assuming that every comma is a separator. If the preview does not line up, verify the file’s delimiter and text-qualifier settings before importing.

Importing into a new table is a useful first pass when the source is unfamiliar. You can inspect the imported fields and clean the data before appending it to a production table. Linking leaves the text file as the source instead of copying its records into an Access table, which may suit a regularly refreshed file, but it does not provide the same control over editing and data types as a properly designed Access table.

Split values already stored in one field

For a simple, consistent pattern, create a select query and use Access expression functions to display the pieces. Suppose a table named Contacts has a field called FullName, and every name is stored as surname, comma, given name. In Query Design, add the table and create calculated fields in the query grid. For example, the expressions below separate the text on the first comma:

LastName: Trim(Left([FullName],InStr([FullName],",")-1))
FirstName: Trim(Mid([FullName],InStr([FullName],",")+1))

InStr finds the comma’s position, Left returns the text before it, and Mid returns the text after it. Trim removes spaces at the ends of each extracted value. For Chen, Maya, the calculated fields return Chen and Maya.

These expressions assume there is a comma in every value. First filter the query to records where InStr([FullName],",") is greater than zero, or otherwise handle values without a comma before using the expressions. Records with no delimiter, blank values, extra commas, or inconsistent ordering need an explicit rule. For instance, Chen, Maya, Lee raises a question: is the intended given name Maya, Lee, or is the record malformed? A query cannot determine that meaning reliably on its own.

Calculated fields in a select query do not change the original FullName values. That makes this a good way to preview the results and identify exceptions. If you need the split values stored permanently, create destination fields in a table and use an append or update query only after checking the query results and making a backup.

Fixed-width text and more complex patterns

Not every text file uses a delimiter. In a fixed-width file, each value occupies a specified range of character positions. In the import wizard, choose Fixed Width and use the preview’s break lines to mark where one field ends and the next begins. This works when the file follows a stable layout, such as a documented export with an account code in characters 1 through 8 and a date in the next positions.

Fixed-width splitting is not the same as splitting on a space. Names, descriptions, and addresses vary in length, so a character-position rule can divide them incorrectly when the source format is not truly fixed. Consult the file’s specification if one is available, and inspect records near the beginning and end of the file as well as unusual or blank records.

More complicated cases include multiple delimiters, quoted separators, optional components, or different formats in the same field. A single Left-and-Mid expression may not be adequate. Depending on the data, you can use several query expressions, a staging table, or a VBA function that applies carefully defined rules. For a recurring import, document the rules and test them against representative exceptions before relying on the output.

Choose a method that fits the task

The right method depends on whether the source is external, whether the split is predictable, and whether the result needs to be saved. Use the following guide:

Delimited file entering Access: Use the text import wizard and verify its preview.
Simple, consistent values in an existing table: Use calculated fields in a select query first.
Permanent fields for repeated database use: Clean the values, then populate appropriately designed fields.
Irregular text or repeatable complex processing: Use a documented transformation process, which may involve VBA or another data-preparation tool.
One-off work where Excel is more convenient: Export or copy the data to Excel, use its Text to Columns feature, and validate the result before bringing it back.

Access is designed to keep data in fields that represent one kind of fact. If you routinely need to search, sort, or report on a component separately, it usually belongs in its own field. A query that calculates a split is helpful for transformation or display, but it is not necessarily a substitute for a well-structured table when the values will be used repeatedly.

Common problems and how to avoid them

The most common error is choosing the wrong delimiter. A comma may be the separator in one file but part of an address or organization name in another. Use a preview and compare the imported columns with the original rows. Also check whether the source uses tabs, semicolons, or another character instead of commas.

Whitespace and inconsistent formatting create another set of problems. A value such as Chen, Maya has two spaces after the comma, while Chen,Maya has none. Trimming the extracted pieces removes surrounding spaces, but it does not fix every inconsistency, such as different name order or an unexpected middle name. Decide how such cases should be represented rather than silently discarding information.

Data types can also be damaged by a seemingly successful import. A code such as 00127 may be an identifier, not a number; importing it as numeric data can remove the leading zeros. Dates can be misread if the source and Windows regional date settings use different orders. Set or verify the field types and compare important values with the source before treating the imported table as authoritative.

Finally, avoid updating the only copy of the original data before you have checked the split. Keep a backup or import into a staging table, run a select query to review the output, and count records before and after a transformation. Confirm that blank values and records that do not fit the expected pattern are accounted for.

Design the destination fields carefully

Splitting a field is often part of a wider data-design decision. If a name is stored as one text value, separating first and last names can help with sorting or mail merges, but real names do not all follow a simple first-name/last-name pattern. A person may have multiple family names, a single name, a suffix, or a preferred display name. Keep the original value if you need to preserve the source exactly, and do not assume a delimiter reveals a person’s preferred name structure.

Choose field sizes and data types that match the values, not just the first few rows in a preview. Identifiers should generally remain text when their formatting matters. If an imported date will be used in date calculations, verify that Access recognizes it as a Date/Time value rather than text. A clear staging process lets you correct data before it reaches tables used by forms, reports, or other queries.

Access is a Windows desktop application; there is no native Microsoft Access desktop version for macOS. On a Mac, you can prepare the text in Excel or another available data tool, or work with Access through a Windows environment. Excel has a Text to Columns command, but it is an Excel feature, not a menu item in Access.

Frequently Asked Questions

Does Access have a Text to Columns button?
Not for splitting values already stored in a table. Access can divide delimited or fixed-width fields during a text-file import, and queries or code can parse existing field values.

How do I split a comma-separated CSV into Access fields?
Use External Data > New Data Source > From File > Text File, choose the file, select the delimited format, and choose comma as the separator. Check the preview to ensure commas inside quoted values are not treated as field breaks.

Can a query split a name into first and last name?
Yes, if the format is consistent. A query can use InStr, Left, and Mid to split at a known delimiter. Names without that delimiter or with a different structure need separate handling.

Will splitting a field change the original data?
A select query that shows calculated fields does not change the stored value. An update or append operation can write values to table fields, so review the results and keep a backup before using one.

Can I use Access on a Mac?
Microsoft Access is a Windows desktop application and does not have a native Mac desktop version. You can use a Windows environment or prepare and split the data in another tool, such as Excel, then move the cleaned data into an Access workflow.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.