Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Separating mixed data into meaningful pieces

Copper Contributor

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.

2 Replies

@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. 

Riny_van_Eekelen_0-1705651413621.png