Convert formatting from time to number

[DaveOb]

I export stock from our POS system into a CSV. Each row is a stock item. It lists into one column the following example 1:1 3:2 4:1 which basically means store one has one item, store 3 has 2 items, etc. I move them out by using text to columns and then cut and paste into a column per store. I know very tedious. Anyway when I want to remove the store prefix (ie 1 to only show the quantity excel keeps formatting as time, which then creates other issues.
I just need a column heading of store number then the quantity listed below
I tried replace, changing formatting and I converted to .xlxs with no luck
Thanks

[AliGW]

Which version of Excel are you using?

[DaveOb]

Version 16.36 its on Office 365 account

[AliGW]

Thanks. Please update your forum profile accordingly.

[AliGW]

Are you able to attach thee .csv file? It might best be imported using PowerQuery.

[DaveOb]

I have done both thanks

[AliGW]

Here's the M Code I used to produce the attached (all easy steps via the PowerQuery UI):

Please Login or Register  to view this content.

You will need to change the bit in red so that the file can refresh from your own data source location.

[DaveOb]

Sorry I am really a novice at this level. PowerQuery is a separate application? And then M Code is for Marco's? Or am I better just getting PQ and using that
I will have to do this a fair bit from now on

[AliGW]

PowerQuery (or Get & Transform) is on the Data ribbon in Excel 365 (it's already built in)

Use the From CSV icon and navigate to the .csv file location. When the import dialog appears, click the Transform button and the data will be loaded into the PQ editor.

Open the Advanced Editor from the ribbon and make a note of the source file path, then overwrite everything in that window with the code above. Now edit the file path appropriately.

Finally click on Close & Load on the left of the main ribbon.

All done!

[DaveOb]

Ok sounds good. Thanks so much for your help

[AliGW]

Have a go - lt me know how you get on.

[DaveOb]

Excel capture 2.JPGExcel capture 1.JPG

Well not that great! I used the same csv file. Hope the screen shots show up otherwise I will add as attachments

[AliGW]

You should add as a workbook, please. Looks like you've messed up one of the steps. Attach the WB and I'll have a look at lunchtime.

[DaveOb]

Thanks. I did try a few times.

[AliGW]

Try this version:

Please Login or Register  to view this content.

Remember to adjust the file path.

[DaveOb]

Wow that worked fine, thanks.

Looking at your 'script' I have to name the column headers the same in a future spreadsheets for this to work on another.
Very excited about this. Going to make things much easier

Thanks

[AliGW]

If you step through each step on the right, you will be able to see how to build up the solution. Everything is done using clicks in the UI - no coding per se (as it's generated for you on the fly). PQ is a great tool to have in your kit.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[DaveOb]

Thanks again