Moving things with similar texts between columns

[BobKarrow]

I have a spreadsheets with hundreds of lines that look like this

Each line is a separate bibliographical citation. I manually add tabs to a Word document to separate the "fields" which are names in the top row. I will transfer these to ProCite, my bibliography program that has 45 fields. I really only need the twelve in row 1, but I need to export all 45 fields (so some are just blank). The problem is that similar bibliographical information falls into different columns when I move it to Excel. For instance in the sheet above, (L180:185) clearly belong in column S, where ISBNs go, (J189:197) belong in column V, (J180:181) belong in column P with the other paginations. Yes, I can sort every column and move things manually but it takes forever. Someone told me about a technique (or add-on?) that could make this easier. Any thoughts? (Sorry for the long posting!)

 

[Drrellik]

It sounds like you are doing a lot of cut and paste as you move from program to program, you can use a formula called FIND to locate specific text like "ISBN" and then trim the information and have it moved to your column, it may take some additional columns or another sheet to sort the information they way you want it.

 

[BobKarrow]

It sounds like you are doing a lot of cut and paste as you move from program to program, you can use a formula called FIND to locate specific text like "ISBN" and then trim the information and have it moved to your column, it may take some additional columns or another sheet to sort the information they way you want it.

But does =FIND work on an entire sheet? I entered =FIND("ISBN",D3:AN299,D3) where D3:AN299 is the entire part of the sheet in which an instance of "ISBN" might be found. It returned "0". I thought that meant there were no instances of "ISBN" there. But then I increased the "within" area to D3:NO299 which includes the column already filled with ISBNs and still got "0". Anyway, how would =FIND report scattered instances of "ISBN" all over the spreadsheet?

 

[BobKarrow]

But does =FIND work on an entire sheet? I entered =FIND("ISBN",D3:AN299,D3) where D3:AN299 is the entire part of the sheet in which an instance of "ISBN" might be found. It returned "0". I thought that meant there were no instances of "ISBN" there. But then I increased the "within" area to D3:NO299 which includes the column already filled with ISBNs and still got "0". Anyway, how would =FIND report scattered instances of "ISBN" all over the spreadsheet?

Oh, I see. If you use the search and replace window, it provides all instances of a word. Yes, that could be useful. I'll play around with it. Thanks for the tip.