Extracting Data

  • Thread starter Thread starter tinab
  • Start date Start date
T

tinab

Following is an example of the type of data I have in Column A of my
spreadsheet. This is just one record there are hundreds that are
similar. I would like to extract the Address and Owner and the 3
lines below that to another column, worksheet or workbook (it doesn't
matter). I am new to Excel so I don't know if this is possibe or not.
I appreciate the help.

ROLL NUMBER : 002-650-067-44-0000
JURISDICTION: 200 -
ADDRESS : 2860- 2858 W First
OWNER : ABC Company LTD
2429 MCBAIN AVE
Washington DC
123456
EQUITY: Registered Owner
LEGAL: PLAN # LOT BLOCK DISTRICT LAND SECTION TOWN RANGE
LOT DISTRICT SHIP
MERIDIAN
 
Excel is designed to work best with one record per row. In other words, all
the data you presented would be strung out on one row. Then you would have
another row with another record.
You can certainly place your data in the manner in which you have, but
then you are unable to use many of the neat features/capabilities of Excel.
I have similar data, one record per row. I also have a button I labeled
View. All I have to do is click anywhere on any row, click on the View
button, and that row's data would be re-arranged to the format in which I
want to see it, and then displayed on the screen. It would then look like
your data. I can then make any changes/additions I wish, click on the
Update button, and all that data would be re-arranged into one row for
storage.
The above is meant as just tips on how to work with Excel. You might
think about how you want to arrange your data.
What you want done will require a looping macro to search for and find each
record and do the copying you want. The actual code of this macro depends
on the layout of your data. Is it all in Column A?? If you wish, send me
what you have and I'll work up something for you. My email address is
(e-mail address removed). Remove the "nop" from this address. HTH Otto
 
One way using some formulas and filtering.

Insert 4 new rows (rows 1 to 4)
Insert a new column A.
Then in A7 (first row with "Address :" in it:
=IF(COUNTIF(B4:B7,"address*")>0,"keep","nope")
drag down

The select column A and do Data|filter|autofilter
filter to show the Keep's.

Copy those visible cells in column B to a new worksheet (cell A1)

But I like Otto's recommendation to put each value in a separate column.

If you want...

Then in B1 of that new worksheet:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"ADDRESS :",""),"OWNER :",""))
and drag down.
(this'll clean up the Address/Owner words/leading spaces)

Then in C1, put this formula.
=INDEX($B:$B,MOD(COLUMN()-3,4)+1+(ROW()-1)*4)
Drag across to F1
and drag down until you start getting 0's.

Select C:F,
edit|copy
Edit|paste special|values

toss columns A:B.
 
I am trying to move the cells containing the ADDRESS & OWNER data to
another column or somehow delete all other rows above and below (until
the next record with the ADDRESS & OWNER data that I need). I have
done a "find record" and it finds all the cells easily but how can I
then move them to the next column? This is a text document from a word
document. Does anyone know if it would be better and if there is a
way to do this in Word? Thanks for the help!
 
Back
Top