split one column data into different columns

Hello there

I have some data that contains all the info in 1 column.
I want a macro to split and distribute the data to different columns.

I have attached the sample sheets.
sample1 is the main sheet
sample2 is the output sheet

I want the data to look like in sample2.

Please help

Thanks

Thanks Jindon
It works like a magic.

Is there a way to happen on the same sheet?

Also is there a way the 'comments' column will stay in the same place, the remaining columns will start from column D.

Thanks

Looks ugly though...

Please Login or Register  to view this content.

Or do you mean like this?

Please Login or Register  to view this content.

after the split, the comments column stay in column A that replaces the original, and the remaining starts from column D.

Can we do this????

See my last post.

I've only just spotted that you put this in the VBA forum - I've been working on a formula solution (attached).

The formulae are all in Sheet2, copied down to row 10. If you want then on one sheet you can just cut and paste to Sheet1.

Hope this helps.

Pete

Pete

The formula works fine but I need the headings also to be taken from the column data without having me to be typed in.

Jindon

Your last post works as I wanted. Does this work for all the rows on the sheets till the 1st empty row??

Should work unless you have blank row(s) within a data range.

Since you don't know the number of headings to be produced in the beginning, 100 is the number of column to be added tentatively.

Please Login or Register  to view this content.

to give you more info, there are lot of other columns with data stays on this sheet. The column B will not contain blank cells which contains serials numbers
column H contains the data to split. after splitting the column H is replaced with only the comments, the remaining data is copied to column R-column Y.

Hope this helps

Then you should have uploaded the file with the similar layouts.

Guess work always takes too much time.

sorry attached the actual datasheet.

Please advice

Need to see your desired result.

Atached the desired result sheet

The rows are merged in the raw data as well as your desired result.

Is that how you wanted?
If so, what is the reason?

Hi

the file was in a different format sorry. FInd attached the copies of the raw data and the desired data sheets.
sorry for back and forth msgs.

THanks

Now you are asking completely different problem.

It can not be done without knowing all header details beforehand.

such as

Overhaul base
Removal Detail
Removal ATA
etc.

The headers will always be the ones as stated below from column R to Y. But these wont appear initially before we run the macro.

Overhaul base Removal Detail Removal ATA Module HSR Module CSR TGT MW N3 MW PELM Score


sorry for all the confusing msgs.

can this be done???

Is there possiblity to have the data like

Overhaul base:abc xyz Removal Detail: def

means item has more than one word.

Then can you upload a sample with the possible pattern of the data as similar as possible to your real data.

Most of the item after column(":") has no space, however you have a space after Removal Detail:.

See H3.

Is that also happen?

This will only work if the items are

All small letters and/or numbers.

Please Login or Register  to view this content.

Attached the sample data and desired data.

You should always remember when you ask questions such like this, data and layouts should be similar to your actual, otherwise it will be just wasting time for both of us.

Please Login or Register  to view this content.

Hi Jindon

Thanks for the help and the code works fine. Can we include the formatting(font,size etc) also for the new columns added to appear as the rest please??

Thanks

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Jindon

For some reason your code doesn't work on this file. see attached.

I have downloaded the same data in csv format and tried running the code but no effect. can you check this please?
also can we leave the comments column as is and just copy the data into different columns instead of cut and paste.

The comments column will have '[LCM]' tag before the data in each cell. // if this info helps

Thanks

Add 2 lines

Please Login or Register  to view this content.

can we leave the comments column as is without trimming the data and just copy the data to the new columns please.
also when I run the macro, the headings row is missing the borders. Can you check this as well.

THanks for your everlasting support and help

Can you show me how exactly you want for the result?

see attacahed

It seems exactly the same as one before.

No, if you do the wrap text on the sheet, you can see the column H will have all the data after copying the data to the new columns from R to Y.

Is this the result that you want? No separation? No formatting? etc.etc.

I need the formatting, I donno what you mean by seperation but I need the comments box to stay as it is without having to trim the details. Just copy the data to respective new columns.

I think I've lost.

You just wait and see if someone else to come in and help you.

Jindon please dont leave this task at its final stage.
I am struggling to explain you the desired result..........leave the formatting issue
can you help with just copying the data to the new columns leaving the comments column as is....

Thanks

What I don't like is the thread asking one by one.

I just need what you have and what you exactly want.

Show me what you exactly want as for the result including cell format/borders/cell color etc.

Please Login or Register  to view this content.

added few things in the code as to get a desired result. I have copied the values to array b so that the split and replace operations will not disturb the values in array a as I wanted the column 8 to stay the same.

but am unable to as the \n charector on column 8 is being replaced with spaces I guess and I am struggling to uderstand what this 2 lines does:

Please Login or Register  to view this content.

can you add the comments for me to understand.

Thanks

Only my guess.
If this is not what you wanted, again need to see exact before/after thing.

Please Login or Register  to view this content.

Not as I wanted. PLease see attached raw and desired results.
Thanks again

Delete 2 lines in red

Please Login or Register  to view this content.

Thanks very much Jindon.

You are a Star