Home

Correctly parsing unformatted text - newbie!

rimibar
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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies