Text to Columns Tool
Use Text To Columns to take the text in 1 column and split the string value into multiple separate columns or rows, based on a 1 or more delimiters.
Tip
This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.
Configure the Tool
Select the Column to Split.
Delimiters: Enter the delimiters you want to use to split the data. Each character is treated independently, meaning you can't delimit on a word. You can use individual characters or the white space characters from this table:
White Space
White Space Character
Tab
\t
New Line
\n
Space
\s
Space or Tab
\s\t
Choose the split method:
Split into Columns: Split a single column of data at each instance of the specified delimiter into multiple columns.
Number of Output Columns: Choose how many columns to create.
Output Column Name: Enter the name for the newly generated columns. The new columns are named as the root name with a serially increasing integer appended.
Extra Characters: Choose what you want to do with any extra characters.
Leave in Last Column: Data that extends past the split is appended to the value in the last column.
Drop with Warning: Data that extends past the split is dropped and a warning is generated indicating that there is excess information.
Drop without Warning: Data that extends past the split is dropped and no warning is generated.
Split into Rows: Split a single column of data at each instance of the specified delimiter into multiple rows. Use a key column in your row so you don't lose track of what value comes from what row.
Select Delimiters to Ignore:
Consecutive: For Split into Rows, empty rows are not created for consecutive delimiters. For Split into Columns, empty columns are populated with Null.
In Brackets: Delimiters and surrounding brackets are included in the cell text.
In Parentheses: Delimiters and surrounding parentheses are included in the cell text.
In Quotation Marks: Delimiters and surrounding quotation marks are included in the cell text.
In Single Quotation Marks: Delimiters and surrounding single quotation marks are included in the cell text.