Extracting Data

[lilanne19]

I have a report that I have to download from one application and then paste that info in to excel. When I copy it into to excel, the entire report is in one column (about 800 rows). What I have been doing is using the left, right and mid formulas but I know there has to be a better way (one with not as many "fixes"). Has anyone else ever needed to split a column

 

[63falcondude]

That's what the Text to Columns feature is for. Check it out. It is under the data tab.

 

[lilanne19]

That's what the Text to Columns feature is for. Check it out. It is under the data tab.

The info is somewhat jumbled so text to columns isn't actually a fix for me.

 

[63falcondude]

Can you share a small sample of your data? Maybe we can come up with something that can work for you.

 

[lilanne19]

Can you share a small sample of your data? Maybe we can come up with something that can work for you.

The below info pastes in to excel as one column and separate rows. In a separate columns I would like to be able to pull the vendor name, invoice number and amt paid. I apologize the example isn't better.
[TABLE="width: 446"]
<colgroup><col width="308" style="width: 231pt; mso-width-source: userset; mso-width-alt: 11264;"> <col width="287" style="width: 215pt; mso-width-source: userset; mso-width-alt: 10496;"> <tbody>[TR]
[TD="width: 308, bgcolor: transparent"][TABLE="width: 446"]
<colgroup><col width="308" style="width: 231pt; mso-width-source: userset; mso-width-alt: 11264;"> <col width="287" style="width: 215pt; mso-width-source: userset; mso-width-alt: 10496;"> <tbody>[TR]
[TD="width: 308, bgcolor: transparent"]Account Number: [/TD]
[TD="width: 287, bgcolor: transparent"]GL Date: 20-DEC-16 Inv Amt: [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Vendor: [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Inv Num: [/TD]
[TD="bgcolor: transparent"]Amt Paid : Invoice Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 287, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

So you have something like this?

Excel 2010
	A
1	Account Number:
2	GL Date: 20-DEC-16 Inv Amt:
3	Vendor:
4	Inv Num:
5	Amt Paid : Invoice Date
Sheet1

What would you want to pull out from it? The only data that I see is a date.

 

[hiker95]

I have a report that I have to download from one application and then paste that info in to excel. When I copy it into to excel, the entire report is in one column (about 800 rows). What I have been doing is using the left, right and mid formulas but I know there has to be a better way (one with not as many "fixes"). Has anyone else ever needed to split a column

lilanne19,

We can not tell where your raw data is located, sheet name(s), cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com