• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split multi line cells into their own rows

Cknight

New Member
Hello-

I have looked all over but can't seem to find a solution to something that should be simple. I pull data from a PDF that looks like the following below. When pasting, it automatically puts all of the data in one cell separated by a line break. The data changes every time it is created and can range from 1 line to 1000 lines.

9161264
9180788
2
1
750
750
XYZ 123
ABC 3456
6
12
72.00
36.00
360.00
189.00
288.00
153.00
48.00
12.75
576.00
153.00

I am looking to have this data separated into its own rows/cells while maintaining the appropriate column. The data will always be in cells A1:J1 every time it is pasted. Either a formula or VBA will work. Thanks so much in advance for looking into this!
 

Chihiro

Excel Ninja
Upload sample workbook with some data pasted in. As is, your data copied will be automatically split into multiple rows using "Match Destination Formatting". But I suspect that is due to copying from site and not from PDF.

65344
 

Chihiro

Excel Ninja
Use following formula in A3 or other location of your choice.

=INDEX(FILTERXML(SUBSTITUTE("<a><b>"&A$1&"</b></a>",CHAR(10),"</b><b>"),"//b"),ROWS(A$1:A1))

Then copy across and down. When you see #REF! error, you've reached end of record. If you don't want the error, you can nest the formula in IFERROR(Formula,"")

65347
 

p45cal

Well-Known Member
You can also do it without formulae:
1. Copy the single row of cells elsewhere but use the TRANSPOSE option when pasting.
You'll get a column of (already selected) cells.
2. Go straight into Text to Columns on the Data tab of the ribbon and in stage 1 choose Delimited, Step 2 untick all the boxes except for Other and place a single character in there using Ctrl+J on the keyboard.
Click Finish.
Copy the result and paste it by transposing again.

If Ctrl+J doesn't work, try holding Alt down while typing 0010 on the numeric keypad part of the keyboard, then releasing the Alt key)
Your cells contain the linefeed character (ascii 10) where it should be split.
 

p45cal

Well-Known Member
Instead of pasting to Notepad, paste to Word, then copy from Word to Excel. The data seemed to be the cleanest when the last step was done with Match destination formatting.
I used the multi-line data you supplied in msg#4, but I suspect it will work directly from where you're getting your data from.
65393
 
Last edited:
Top