Correctly parsing unformatted text - newbie!

Occasional Visitor

Hi all,


Even though I've used Excel for years, I am struggling to remember how to correctly parse some unformatted data I've pasted into Excel.


Here is an example of the data which is in text format:


Care UK Nursing & Residential Care Services 9.2 121 CH, 9 ADC • P
Housing & Care 21 1 CH, 1 ADC, 119 ECH • V


I have provided two examples to explain my conundrum - I'd like column 1 to contain the name  - above either "Care UK Nursing & Residential Care Services" or "Housing & Care 21". The next number (e.g. 9.2) represents a score, but not ALL of the homes have a score (making it more complex). The next column contains the number of homes e.g. 121 CH or 1CH. The next columns should be separated by the comma - which there may be differing numbers of. For example the first entry has 2 column types for CH and ADC, whereas the 2nd example should have 3 - for CH, ADC and ECH. And (finally!!!) the last column should contain everything after the "•".


Am I dreaming that this can be done in Excel or is there a Guru out there who can make sense of it? 🙂


In the interests of full disclosure this is for some non-commercial research I am doing and the data is courtesy of https://www.carehome.co.uk/caregroups/.


Thanks a million!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies