Excel Help..!!

[Columbo]

Below is a set of data exported from our WMS.

Is there a way I can insert a column or extract the data between LOC: & LPN: as it exports them all in them same cell.

Any help appreciated.

 

[peterattheboro]

Don't think so.

You'd have to copy and paste each row into it's own cell I believe.

I may be wrong though.

 

[HEADRAT]

http://uk.youtube.com/watch?v=OWlG6bwtQq8&feature=related

 

[Dolph]

You could set the data import to seperate on a : and spaces, what would give you columns with entries split at every space and every : Which should work for you ok.

How are you actually importing the data into the spreadsheet, and do you have any other export options from the database?

 

[Meeko]

What format does your WMS export the data in? If you can get it to export to a .CSV file then you will be able to import that into Excel with the delimiter set as a 'space' (like the guy above has said) which will put each entry in its own cell.

 

[div0]

It depends exactly what you want to do, but if you select that column and chose 'Data' (top menu bar) and then 'Text to Columns', you could chose 'Fixed Width' which would split every 'space' into a new column. Or you could chose 'Delimited' and split on 'space' and 'colon'.

That would make it easy to either grab a certain section of the data, or to insert a new column in between any sections of the data.

You can then use the CONCATENATE function in another column to rejoin all the data together if you want.

 

[Participant]

Or use =MID(A1,FIND("LOC:",A1,1),FIND("LPN:",A1,1)), or something like it.

 

[Van_Dammesque]

Text to Columns function?

 

[Participant]

select that column and chose 'Data' (top menu bar) and then 'Text to Columns'

Text to Columns function?

 

[Columbo]

Got it sorted cheers guys by using the text import wizard.

I replaced the LOC: and LPN: with a comma and and saved it as a text file, then imported it and delimited it by , and it inserted a column.

Thanks for the help..!!