Hello
I have exported some data from Magento into Excel and would like some advice on how best to tackle this problem. I have a field that lists supplier details - there are huge differences in what the field contains - there may be one contact, there may be several, some have opening hours in as seen below, most of them just have suppliers with addresses and phone numbers. I've used Clean and pasted text as values. Here are some examples of what I have got:
Pennine Helicopters LtdOakdene FarmSaddleworthGreater ManchesterOL3 5LU0161 789 3030
Cliveden HouseClivedenTaplowBerkshireSL6 0JF01628 668561
Ayurveda Retreat32 Friar StreetReadingRG1 1DXTo book you massage T: 0118 958 8190Opening Hours:Mon-Fri: 9.00am - 8.00pmSat: 9.00am - 6.00pmSun: 10.30am - 6.00pm
Flawless Studio 14, 14 Little Lever Street, Manchester, Lancashire M1 1HR Tel: 0844 874 5000Flawless Studio 16 Brentnall Street, Middlesbrough, TS1 5AP Tel: 0844 874 5000
You can see from the data that it's a mess with no spaces in between words. Text to columns doesn't seem to work when I tried it on a couple of entries. What I'm after is to:
to have a column for business name
to have a column for Address but there needs to be a comma between address lines.
to have a separate column for postcode ideally
to have a column for telephone number
opening hours are not important.
Can anybody advise me on how to tackle this?
Thank you.
I have exported some data from Magento into Excel and would like some advice on how best to tackle this problem. I have a field that lists supplier details - there are huge differences in what the field contains - there may be one contact, there may be several, some have opening hours in as seen below, most of them just have suppliers with addresses and phone numbers. I've used Clean and pasted text as values. Here are some examples of what I have got:
Pennine Helicopters LtdOakdene FarmSaddleworthGreater ManchesterOL3 5LU0161 789 3030
Cliveden HouseClivedenTaplowBerkshireSL6 0JF01628 668561
Ayurveda Retreat32 Friar StreetReadingRG1 1DXTo book you massage T: 0118 958 8190Opening Hours:Mon-Fri: 9.00am - 8.00pmSat: 9.00am - 6.00pmSun: 10.30am - 6.00pm
Flawless Studio 14, 14 Little Lever Street, Manchester, Lancashire M1 1HR Tel: 0844 874 5000Flawless Studio 16 Brentnall Street, Middlesbrough, TS1 5AP Tel: 0844 874 5000
You can see from the data that it's a mess with no spaces in between words. Text to columns doesn't seem to work when I tried it on a couple of entries. What I'm after is to:
to have a column for business name
to have a column for Address but there needs to be a comma between address lines.
to have a separate column for postcode ideally
to have a column for telephone number
opening hours are not important.
Can anybody advise me on how to tackle this?
Thank you.
