Seperating Data

[mesolomo]

Hi,

This is my first posting, but I'm hoping you all can help:

I am copying data from a PDF. The data I need is in columns, spread over hundreds of sheets. I would like the data in the same layout (columns), but need it in excel instead of the PDF sheets.

I've tried copying and pasting into Excel, but the problem is that what is in columns on the PDF ends up as multiple rows beneath eachother, all in column A, on excel.

I've tried Text to columns, but that seems to only work if all the data you need to seperate is in the same row. What I need to ultimately appear in one row is currently displaying in three rows, one beneath the other. Is there a way for excel to automatically move what's in cell A2, for example, to B1 (and do this for all data, all the way down the sheet, that's similar to that currently in A2?

(If it would be useful for me to post a screenshot, could someone reply with how to do that in the most version of Excel?) Thanks!!!

 

[wsjackman]

One problem I'm having though is sometimes after I transpose the data, once I make any changes to any other rows, all the data in the two new columns (B and C) change to "#REF!". Why is that? Am I doing something wrong transposing the data?

I think it may have something to do with Pasting Special Values. I found the button for that, but I'm not sure I understand what it does or exactly how to do it. Could you explain that a bit more to me?
QUOTE]

you are right it is a paste special value
afetr you have copied the cells down , select columns B and C
copy these columns
then edit-paste special-values

if you look at B1 (or whatever is your top row) it should show a value and not a formula, paste special value takes whatever you have copied and changes any formula to a constant, either text or numeric, depending on what the result of the formula was.

if you have problems, try it on a small formula to see if it is working until you hve the mechanism down

 

[mesolomo]

Something really strange is happening now. I went to paste a new page of data into a new worksheet, and it's formatting completely differently. Whereas yesterday it would paste all in column A, with what was in a separate column in the PDF now in a row underneath the data from the column before it in the spreadsheet, now it is pasting everything that's separated by a space into a different column. This is even more difficult to work with than before!

It basically seems like it is keeping the "text to column" distinction that I had set in the last spreadsheet, but for all the data I paste in the new worksheet. Is that what's happening? How do I turn it off?

I've tried using a new workbook, and have tried going back and using the same data as yesterday to see if it was formatted the same way, but even the old data is now posting in all of these separate cells. Any ideas?

 

[wsjackman]

I have no clue as to what is happening in the text to column, and do not have a recommendation?

what is wrong with what it is doing now? it seems like this is what you wanted originally?

 

[mesolomo]

It has the essence of what I wanted originally, but the actual format is completely unworkable.

Because not every group of data has the exact same number of words, if it breaks them up into a different column every time there's a space, then what I need to all be in one column is spread over many columns. On top of that, some of it posts in new columns and some shifts to the row below. I don't see any way to sort it except to go through and delete and move cells manually, and with thousands of rows, that's just not possible.

Also, in some of the cells, where there is supposed to be a numeric value is instead "#####'. Do you know why that is and how I can recover the number that should be there?

 

[mesolomo]

wsjackman,

I have another issue you might have an idea about:

Is there a way to select or filter all rows that have a blank cell above it? It's one of the only ways I can think of to isolate the data that I want to get rid of.

 

[wsjackman]

It has the essence of what I wanted originally, but the actual format is completely unworkable.

Because not every group of data has the exact same number of words, if it breaks them up into a different column every time there's a space, then what I need to all be in one column is spread over many columns.

do not use a space as a dilimiter. there are a couple of possiblities. something is causing it to sometimes go to the row below.

Take a full line, preferable one which wants to go to new lines
put it in C1 in a new sheet.

In A1 enter
=code(mid(c1,row(a1)))
copy down until you have all the characters listed

in b1
=char(a1) and copy down.

at some point there will probably be a character which did not show up in the original phrase about where there was a separation of some type in the original document

in C2 enter
=substitute(c1, (the char you just found), "#")
or use something other than # if it is otherwise found in the file

in text to column try using # as your delimitor
​

On top of that, some of it posts in new columns and some shifts to the row below. I don't see any way to sort it except to go through and delete and move cells manually, and with thousands of rows, that's just not possible.

Also, in some of the cells, where there is supposed to be a numeric value is instead "#####'.

You may need to increase cell width, some number formats need a minimum width to be able to display.

​

Do you know why that is and how I can recover the number that should be there?

Is there a way to select or filter all rows that have a blank cell above it? It's one of the only ways I can think of to isolate the data that I want to get rid of.

insert a new column a
in a2 enter
=if(b1="",1,"")
copy down
filter column A for 1s