Split multi line cells into their own rows

[Cknight]

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]

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.

 

[Cknight]

Uploaded. Sorry about that

 

[Cknight]

Here is another file to show what it looks like with more than 2 entries that need to be separated

 

[Chihiro]

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,"")

 

[Cknight]

That works perfectly. Thanks so much for your help!!

 

[shrivallabha]

Just curious, try to paste data from PDF to "Notepad".

And then copy from Notepad, paste in Excel.

 

[Cknight]

Just curious, try to paste data from PDF to "Notepad".

And then copy from Notepad, paste in Excel.

Pasting into Notepad puts each entry into its own row for me but all in column A.

9161264
9180788
2
1
XYZ 123
ABC 3456
6
12

 

[p45cal]

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.

 

[shrivallabha]

Pasting into Notepad puts each entry into its own row for me but all in column A.

9161264
9180788
2
1
XYZ 123
ABC 3456
6
12

You should be able to do "Text To Columns" as described by @p45cal and get your data.

 

[p45cal]

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.

 

[kaye17]

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.

THIS!!! You just save me about 4 hours of data entry because nothing was working until I found THIS!!!! Thank you!!!!!