Splitting one cell information into multiple

[Dhruva101]

I have following information stuffed into one cell (C29)
ABCNHV.FABC
ABCNHN.VXASE
ABCNHN.F8909
ABCNHN.VASS.ASSDTRAD
ABCNN.XAAA.REVSLEXT
ABCNN.AAAA.REVACCPT

I want each line in a seperate cell.

So the above should be spread out in total 6 rows.. i.E C29,C30,C31,C32,C33,C34.

Any suggestions?

[Mallycat]

Something in the cell is wrapping the text into new lines in the cell. If you only have 1 cell like this, just copy it into word, make sure there is a paragraph mark at the end of each line, then cut and paste it back into excel.

[Dhruva101]

This is not really one cell. There are many for which I need to do it manually. That is the reason I am looking for a repeatable process.

[Bryan Hessey]

I have following information stuffed into one cell (C29)
ABCNHV.FABC
ABCNHN.VXASE
ABCNHN.F8909
ABCNHN.VASS.ASSDTRAD
ABCNN.XAAA.REVSLEXT
ABCNN.AAAA.REVACCPT

I want each line in a seperate cell.

So the above should be spread out in total 6 rows.. i.E C29,C30,C31,C32,C33,C34.

Any suggestions?

Did you follow the 'copy to Word' suggestion with the whole column?

You cannot easily do this by formula, if the 'Word' fails you will need to have some VB Code do the split, can you copy a couple of rows to a test.xls and post here (as a .zip) for testing (you can remove all other data, just a couple of those cells should suffice).
plus, did you want to add rows for integrity with other data in the sheet?
---

[Dhruva101]

Did you follow the 'copy to Word' suggestion with the whole column?

How do you do this "copy to word" ? I don't think I have done this. Please let me know so that I can try this one. If this doesn't work then I would send an attachment with some sample rows.


Thank You..

[Bryan Hessey]

How do you do this "copy to word" ? I don't think I have done this. Please let me know so that I can try this one. If this doesn't work then I would send an attachment with some sample rows.


Thank You..

A sample post of a few lines would be good, save as a new workbook and from the file list (in Explore) rightmouse and 'Add to Archive' named as a .zip

Cheers
--

[Dhruva101]

Hi Bryan,

Please find enclosed the spread sheet. It has some sample data that I need to format. I have also stated how I would like to have the output.

Please suggest me the way to quicken the process.

Thank you...

[Bryan Hessey]

Hi Bryan,

Please find enclosed the spread sheet. It has some sample data that I need to format. I have also stated how I would like to have the output.

Please suggest me the way to quicken the process.

Thank you...

Please Login or Register  to view this content.

as per the attached.

Run the Macro.

note, for the 1) 2) etc, do Edit Replace and remove the 6 items

---

[Dhruva101]

Thanks a TON Bryan. This worked like a charm.

How do we write these Macors ? I know a little bit of VB. Does that help?
Any ideas to start with, at least If I could able to write a little macros that
would be great..


How do you come up with the key words "worksheets" and "Range" etc ?

[Bryan Hessey]

Thanks a TON Bryan. This worked like a charm.

Good to see it worked

How do we write these Macors ? I know a little bit of VB. Does that help?

The easy way to start is to Record Macro and do what you need, then Stop Recording and Edit the macro (Tools, Macros etc)

Any ideas to start with, at least If I could able to write a little macros that
would be great..


How do you come up with the key words "worksheets" and "Range" etc ?

With great difficulty, I barely understand the WorkBook, WorkSheet, Range and Cell objects and their relationship to the various 'Insert/add' etc options, and like most people I copy the various bits of code from a working sheet of code, be it mine or someone else's.

There are various books that help, but I have not (yet) read any, nor attended any course in Excel nor VB. (one day . . . maybe)

Hope that helps
---