Text to Columns

[midfieldgeneral11]

Good Morning

Hope you can help.

Have a column of 10,000 rows.

The column is called Business Type

This column is one field which is formulated in two ways.

The first format one cell with each business class separated by a comma.

Marine,Motor Fleet,Specie,Cyber,Hull,Cargo

The second format is one cell with each business class separated by a enter

Marine
Motor Fleet
Specie
Cyber
Hull
Cargo

I would like to split this cell up into separate cells is there a way I am spit this field up even though I have two different formatting issues?

Simon

 

[alansidman]

your first delimeter is a comma, the second one is CTL + J. This should do it for you. Put the second one in the Other text box

 

[midfieldgeneral11]

The above works for the Delimiter. But for number two where the field has a speciality after each button like below

Schemes
Commercial Combined
Contractors All Risks
Combined Liability
High Risk Liability Trades
Excess Layer
Property Owners
High Net Worth Household Insurance
Professional Indemnity
Excess Protection

This is showing just the schemes and deleting the rest. can you advise?

Should I be splitting the list up if so is there a formulae I can use to delete before applying

S

 

[midfieldgeneral11]

Data in one Column?

I have a spreadsheet which is 1,000 rows for which one cell (Column B) has now been split into several fields from Column C to Column AA

Some rows are only to Column A to B but some due to the split of data are to Column A to Column DD

I need to collate all rows in one column in another spreadsheet. Can this be done?

Where you would copy one row and transpose into another spreadsheet. Is there a way I can transpose 1,000 rows in one column into another spreadsheet.

Your help would be appreciated.

Thanks

Simon

 

[RedBeard]

Re: Data in one Column?

I'm trying to understand how this looks in Excel, but I just can't understand it.
Can you post a screenshot?

 

[Joe4]

Re: Data in one Column?

Your two threads are so similar, I have merged them together into one thread.

Let's try to keep it to one thread, as per rule 12 here: Forum Rules