Yes. In JMP, select the text column you want to divide, then choose Cols > Utilities > Text to Columns and specify the delimiter, such as a comma, tab, or space. JMP creates separate columns from the delimited values; check the results and column types before using them in an analysis.
What Text to Columns does in JMP
Text to Columns takes values stored together in one column and separates them into multiple columns at a chosen boundary. For example, a field containing North,2025,42 can be split at each comma into three values: North, 2025, and 42. This is useful when a file has imported several pieces of information into one field, or when a supplier, survey, or system export stores records as delimiter-separated text.
The operation changes the shape of a JMP data table. Before splitting, one column holds the combined strings; afterward, each part occupies its own column in the same rows. It is different from Tables > Split, which reshapes a table using values in one column to create new columns, and from Tables > Stack, which gathers columns into a longer structure. Text to Columns is for dividing text within a cell-like table value.
It also is not a general-purpose repair for every malformed import. The delimiter must identify the boundary consistently. If the source uses a complex format, quoted delimiters, or irregular field rules, inspect the raw data and consider importing it with the appropriate settings rather than splitting blindly.
How to split a column
- Keep a recoverable copy. Save the table or duplicate the original column before making a change. This makes it easy to compare the source with the split result and recover values if the delimiter choice was wrong.
- Select the source column. Click its column header in the JMP data table. If the command is unavailable, confirm that a data table is active and that a suitable column is selected.
- Open the command. Choose Cols > Utilities > Text to Columns. JMP versions can differ in their dialogs and available options, so read the controls shown in your installed release.
- Choose the separator. Set the delimiter that appears between fields, such as a comma, tab, semicolon, or space. If the source uses a less common separator, use the dialog’s available custom-delimiter control if offered.
- Review the split settings and run the operation. Use the displayed settings to determine how JMP handles repeated delimiters and the resulting columns. Avoid assuming that consecutive separators should be collapsed: in some datasets they indicate an empty field.
- Inspect and rename the output. Compare several rows, including unusual or incomplete ones, with the original. Give the new columns meaningful names and set their data types and modeling types appropriately.
For example, splitting R17|West|In service on the vertical bar should yield an identifier, a region, and a status. The delimiter is the vertical bar, not the letters around it. If the source contains spaces around the separator, such as R17 | West | In service, decide whether those spaces belong in the values and check whether the split settings remove or retain them. If they remain, trim or clean the resulting text before analysis.
Choosing the right delimiter
The delimiter is the character or boundary that separates fields in the original value. Choose a character that marks field boundaries in the data, not merely one that happens to appear often. Common choices include commas in simple comma-separated text, tabs in copied spreadsheet data, semicolons in some regional exports, and a pipe character in system-generated records.
- Comma: A reasonable choice for simple values such as
blue,large,12, but risky if a field itself can contain commas. - Tab: Often appropriate for text copied from a spreadsheet or exported as tab-delimited data.
- Space: Suitable for fixed, single-word fields, but usually unsuitable for names, addresses, or descriptions that contain spaces.
- Custom character: A distinctive separator such as
|can be safer when it does not occur inside the fields.
Take care with repeated separators. In West,,42, the empty position between the commas may mean that a value is missing, while in another source repeated spaces may simply be formatting. Collapsing repeated separators in the first case could shift the meaning of the fields. Use a few representative rows to establish what the source convention means before processing the entire column.
Examples and a practical review
Suppose a product column contains AX-104;Hardware;19.95. Splitting on the semicolon produces a product code, category, and price. Check a row with a missing price, a category containing punctuation, and a value with extra spaces. If every row follows the same pattern, the result is predictable. If some rows have two separators and others have three, the output may not line up consistently, and those exceptions need attention.
For a person field such as Jordan Lee, splitting on spaces may appear to create first and last name columns, but it fails for names with middle names, compound surnames, or a single name. Likewise, splitting 12 Main Street, Apt 4 on commas can create address components, but it does not reliably identify street, city, and region unless the source has a consistent, documented structure. Text splitting follows delimiters; it does not infer what a field means.
After splitting, sample the first, middle, and last parts of the table, plus rows that look unusual. Check that the number of resulting fields is sensible and that values have not been shifted. A quick visual review is especially important before running summaries, joins, or models, because a split that looks mostly correct can still leave a small number of misleading records.
Data types, missing values, and leading zeros
Text splitting and data interpretation are separate concerns. A resulting column may be stored as character text even when its contents look numeric, or JMP may assign a type or modeling role that is not appropriate for the intended analysis. In JMP, a column’s data type describes how its values are stored, while its modeling type describes how JMP should treat those values, such as continuous or nominal. Check both using the column information or column properties after splitting.
Identifiers are a common trap. A code such as 00127 is not necessarily a number to calculate with; converting it to numeric can remove its leading zeros. Postal codes, account codes, and product IDs are generally identifiers, so preserve them as character data when their exact characters matter. Conversely, a price or measurement intended for arithmetic should be numeric, and text symbols or stray spaces may need to be cleaned before conversion.
Missing fields deserve their own check. An empty field between delimiters can have a different meaning from a row that ends early or has no delimiter at all. Confirm how the particular JMP release represents these cases in the output, and do not treat a blank as zero unless the data definition supports that interpretation. Record what the source means, especially when the columns will be used in statistical analysis.
Common problems and how to avoid them
- The wrong number of columns appears. The chosen delimiter may also occur inside values, or the source may use more than one delimiter. Inspect raw examples and try a more distinctive separator where possible.
- Words are broken unexpectedly. A space delimiter splits at every matching boundary, including spaces inside multiword values. Use a different separator or a more structured source field instead of assuming spaces mark only one boundary.
- Values appear shifted or blank. Consecutive delimiters, missing trailing fields, and inconsistent row formats can create empty positions. Compare the result with the original row by row for exceptions.
- Numbers do not behave like numbers. The split result may be character data or contain spaces and symbols. Confirm the data type, clean the values if needed, and convert only when the contents are genuinely numeric.
- Quoted commas cause trouble. A value such as
Smith, "Research, West", 12contains a comma inside a quoted field. A simple delimiter split may not interpret quotation marks as full CSV rules do. For quote-aware CSV data, import the file with appropriate CSV or text-import settings and inspect the parsed columns rather than assuming Text to Columns will honor every quoting convention.
If a test split produces an unexpected result, undo or close without saving if appropriate, then return to the untouched source and test a different delimiter or import method. Do not repeatedly split already split output: it becomes harder to distinguish original values from generated ones.
When to use another JMP method
Text to Columns is a good fit when one field has a stable delimiter and the goal is simply to separate its pieces. Choose a different approach when the task is structurally different. For example, use Tables > Split when values in one column should become separate columns based on categories, and use Tables > Stack when several columns should be gathered into one column with an identifying label.
For a one-time clean import, it can be better to set the delimiter and import options when bringing in a text or CSV file. That avoids first loading a combined field and then repairing it. If the source is inconsistent or requires conditional parsing, a formula column, a documented script, or preprocessing in the system that creates the file may be more maintainable. Choose the method that preserves the original data and makes the transformation easy to repeat and audit.
Excel’s Data > Text to Columns feature is a separate command with its own wizard and options. The concepts are similar, but its menus and behavior should not be used as a guide to JMP’s exact dialog. In JMP, begin with the selected column and the Cols menu path above, then follow the controls available in your installed version.
Frequently Asked Questions
Where is Text to Columns in JMP?
Select the column in a JMP data table, then choose Cols > Utilities > Text to Columns. If you cannot find the command, check that a data table is active and consult the help for your installed JMP version, because menu details can vary by release.
Can JMP split a column on a comma?
Yes. Choose comma as the delimiter when the comma consistently separates fields. If commas can also appear inside a field, especially in quoted CSV text, test carefully or use the file-import options that parse the source format.
Can I split text on spaces in JMP?
Yes, if spaces reliably separate the values. It is a poor choice for multiword names, addresses, or descriptions because each internal space can create another boundary. A dedicated separator or a more structured source is safer for those fields.
Will splitting preserve leading zeros?
Do not assume it will. Check the resulting column’s data type and confirm that an identifier such as 00127 remains exactly as written. Keep identifiers as character data when leading zeros are meaningful.
Does Text to Columns understand quoted CSV fields?
A basic delimiter split should not be treated as a full CSV parser. If a comma inside quotation marks belongs to one field, use JMP’s text or CSV import process with appropriate parsing settings, then verify the imported rows.
What should I do if the command is missing?
Make sure a JMP data table is active and a column is selected, then check the Cols menu and your release’s documentation. If the source is an external file, importing it with the right delimiter settings may be more appropriate than splitting a column afterward.
Related
- Redo in Excel: How to Restore an Undone Action
- Easiest Way to Superscript in Excel: Quick Steps and Examples
- Combine Excel Spreadsheets: Easy Methods for Your Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Convert Hours to a Number in Excel: Time to DecimalTo convert an Excel time value to decimal hours, multiply it by 24. If the time is in…
- Excel Workbook Shortcut Keys for Everyday TasksFor Excel for Microsoft 365 on Windows, the most useful workbook shortcuts are Ctrl+N to create a workbook,…
- Google Sheets Scenario Filter: How to Rebuild ItGoogle Sheets does not have a built-in feature called Scenario Filter. To rebuild one, either create a filter…
- Compare Two Excel Sheets Easily in Microsoft ExcelTo compare two Excel sheets quickly, put their values in the same row and column order, then use…
- How to Create a Badge Template in Word for PrintingTo create a badge template in Word, set up a page for your badge inserts, place a precisely…
- Excel Number Format for Negative Numbers in ParenthesesTo show negative numbers in parentheses in Excel, select the cells, press Ctrl+1, choose Number > Custom, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.