Query on Text to Columns

[sats43]

Hi,

Attached is the sample working file, i have a user data embedded into the comments columns for each user reference. However, i need to extract the wrapped comments column.

Please advise

[Olly]

Try something like:

Please Login or Register  to view this content.

Adjust to suit.

[MatrixMan]

Hi Sats ... a line return within a cell is a character which code is chr(10). So if you write code to run through each row, look at the comments cell and determine whether that character is present, you can split out the text and create duplicate rows with the separate comments. I suggest using the Instr function; if the return value is greater than zero, there are line returns in the cell; if it = zero, there are not. The same approach, looking for the ^ character, can be used to populate the separate columns for each line found. So this will find the three lines (run it in a loop until it = 0):

Please Login or Register  to view this content.

and this will find the three values for the columns:

Please Login or Register  to view this content.

[morevijay1982]

attached for your reff

regards

[sats43]

Thanks to you both .. but your solutions are bouncing my head, can you please incorporate this code in my excel file and send it back to me. I tried the above options .. but i believe i am doing something wrong.

[MatrixMan]

XL Split Function.xlsm

I like Olly's solution ... Split function .. nice :-)

One very small addition under the comment:

Please Login or Register  to view this content.

[sats43]

Great ! working perfectly

[MatrixMan]

Please give credit to Olly :-)

[Olly]

Glad it does what you wanted - thanks for the feedback. And thanks MatrixMan for uploading a sample workbook - appreciate the help

[sats43]

Hi Olly,

I need to run this report and paste it into another sheet next to it .. how do i do that?

Thanks

[Olly]

Something like:

Please Login or Register  to view this content.

Change the worksheet names as necessary.

[sats43]

Thanks Olly,

But It says subscription out of range, please suggest

[Olly]

Attach your file.

[sats43]

Hi Olly,

Here is the attachment.

[Olly]

You haven't changed the worksheet names...

In your workbook, Sheet2 is the source, and Sheet1 is the target...

So swap those names over in the code:

Please Login or Register  to view this content.

[sats43]

Gotcha .. my bad .. thanks