Forum Discussion
Separating mixed data into meaningful pieces
Hello all, My question is about converting the text into columns with the Excel. All i want is to convert the mixed text data in a column into meaningful data pieces with an elegant/smart move.
I am extracting data from a different software and this data comes in a column with different character lengths and number digits as I give as an example below;
(The mixed text data with different letter and numerical lengths)
143B0
144B0
2545B1500
159B2700
123P39
1822P39
With above example given, I want to get them in separate columns with below order:
(The separation is denoted with "-")
143 - B0
143 - B0
144 - B0
2545 - B1500
159 - B2700
123 - P39
1822 - P39
I want to separate this data as before and after the letter(like B and P) as shown above.
How can I use a formula or conditioning method to achieve this?
Thank you very much.
Floyd35 Assuming your real data contain more than just a few rows, I suggest you look into Power Query. Connect to the table and split the column where where a digit is followed by a character.
Attached and example.
