How to unconcatenate in Excel
By
SpreadCheaters
You can watch a video tutorial here.
The term ‘unconcatenate’ refers to separating the values in a cell. When working with data in Excel, you will frequently need to separate data from one column to multiple columns. This is usually done to make the data more granular so that it is easy to summarize and analyze the data. Also when working on data imported from other applications, you may need to clean up the text by weeding out junk data. Excel has a tool that separates text into columns based either on a delimiter or on the width of the text.
Step 1 – Analyze the data
– Check the data to see how the fields are separated
– In this example, they are separated by a hyphen(“-“)
Step 2 – Open the Convert Text to Columns Wizard
– Select the data
– Go to Data > Data Tools
– Click on Text to Columns
Step 3 – Choose the file type
– Select the Delimited option as the data is separated (or delimited) by a hyphen
– Click on Next
Step 4 – Choose the delimiter
– Under Delimiters, choose Other and type a hyphen (-)
– Check how the data has been separated in the Data preview
Step 5 – Choose the format and destination
– Under Column data format, choose General
– Select the Destination for the data
– Click Finish
Step 6 – Check the result
– The values are separated into two columns
