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!
