Magento to Excel export

ddkmich

New Member
Joined
Dec 14, 2017
Messages
1
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top