A question about defining fields in a text list of records

[philipgalitzine]

Hey all,

I'm a Word novice, and I have a question for an advanced user. I've
got a document filled with text that is layed out in the following
way:

Name: John Smith
Phone Number: 555-1212
Address: 1234 Main Street, NY, NY

Name: John Jones
Phone Number: 555-2000
Address: 4567 Main Street, NY, NY

etc., etc.

I would like to convert this text to a table with columns labeled
Name, Address, Phone Number, etc. and a row for each individual, for
eventual import into Excel. Is there a quick way to do this, or
perhaps a macro or some such that I could use? Any help would be
greatly appreciated!

Thanks,

Phil

 

[Clive Huggan]

Hello Phil,

If your data is consistent (e.g. no blank where, say, a phone number should
be) you can simply select the text then choose Table menu => Convert =>
Convert text to table, then specify the number of columns etc. Then format
the table.

In other contexts, I pre-format my tables to exactly what I want, then make
them into an AutoText item. So, for example, if I want a 6-column table with
only horizontal borders visible and text of a certain size, with specific
leading, I key in a 3-character code then insert as AutoText -- voila! the
table appears. If that's of interest (and any of the other techniques in
there), take a look at page 126 of some notes on the way I use Word for the
Mac, titled "Bend Word to Your Will", which are available as a free download
from the Word MVPs' website
(http://word.mvps.org/Mac/Bend/BendWordToYourWill.html).

[Note: "Bend Word to your will" is designed to be used electronically and
most subjects are self-contained dictionary-style entries. If you decide to
read more widely than the item I've referred to, it's important to read the
front end of the document -- especially pages 3 and 5 -- so you can select
some Word settings that will allow you to use the document effectively.]

Cheers,

Clive Huggan
Canberra, Australia
(My time zone is 5-11 hours different from North America and Europe, so my
follow-on responses to those regions can be delayed)
============================================================
* SUGGESTION -- KEEP REVISITING AFTER YOU POST: If you post a question, keep
re-visiting the newsgroup for several days after the first response comes
in. Sometimes it takes a few responses before the best or complete solution
is provided; sometimes you'll be asked for further information. Good tips
about getting the best out of posting are at
http://word.mvps.org/Mac/AccessNewsgroups.html and
http://word.mvps.org/FindHelp/Posting.htm (if you use Safari you may see a
blank page and have to hit the circular arrow icon -- "Reload the current
page" -- two or more times).
============================================================

 

[CyberTaz]

Hi Phil -

Like Clive says, the key is consistency in how the content is currently
stored. Don't worry about a macro... The process will take only a minute or
two regardless of how many records. Automation is only worthwhile if you
need to do this repeatedly. If your specimens are accurate I'd further
suggest the following before converting the list to a table:

1- Find & Replace for each ³label², such as Find What: Name:{space}
Replace With: leave blank,

2- Find & Replace ,{space} with ^p (to split the street,city & state)

3- Find & Replace ^p^p with ^p (to get rid of the "empty paragraphs)

4- Apple+A to select all, Table>Convert>Text to Table: Specify Number of
Columns: 5, Select ³Autofit to Contents², Separate Text At: Paragraphs

5- Save As whatever you want to call it, but specify Text Only in the
Format list when you save. I would also not worry about captions or
formatting - that can all be added once the data is imported to Excel.

Note: In Steps 1 & 2, {space} represents just the press of the spacebar -
don't type the brackets or the word. Also, the number of columns (5) is
based on the sample data. If there are more fields (such as zip code) you
can adjust the number accordingly.

Also, there are some relatively simple ways to get the first-last names,
street-city-state separated into separate fields (columns) once you get the
list into Excel.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[philipgalitzine]

Hi Phil -

Like Clive says, the key is consistency in how the content is currently
stored. Don't worry about a macro... The process will take only a minute or
two regardless of how many records. Automation is only worthwhile if you
need to do this repeatedly. If your specimens are accurate I'd further
suggest the following before converting the list to a table:

1- Find & Replace for each ³label², such as Find What: Name:{space}
Replace With: leave blank,

2- Find & Replace ,{space} with ^p (to split the street,city & state)

3- Find & Replace ^p^p with ^p (to get rid of the "empty paragraphs)

4- Apple+A to select all, Table>Convert>Text to Table: Specify Number of
Columns: 5, Select ³Autofit to Contents², Separate Text At: Paragraphs

5- Save As whatever you want to call it, but specify Text Only in the
Format list when you save. I would also not worry about captions or
formatting - that can all be added once the data is imported to Excel.

Note: In Steps 1 & 2, {space} represents just the press of the spacebar -
don't type the brackets or the word. Also, the number of columns (5) is
based on the sample data. If there are more fields (such as zip code) you
can adjust the number accordingly.

Also, there are some relatively simple ways to get the first-last names,
street-city-state separated into separate fields (columns) once you get the
list into Excel.

HTH |:>)
Bob Jones
[MVP] Office:Mac

Hey guys, thanks for the help! I think the problem now is missing
fields. The data set is not as clean as I'd like, and what I need to
do now is insert fields that don't necessarily have anything in them.
For example:

Name: John Smith
Phone Number: 555-1212
Address: 1234 Main Street, NY, NY
Fax Number: 555-1000

Name: John Jones
Phone Number: 555-2000
Address: 4567 Main Street, NY, NY
Fax Number: <blank>

The second entry has no fax number, but I need the field in order to
make the table columns line up properly. Is there a way to
automatically insert fields? For example, if I know that "Fax Number"
always comes after "Address", is there a script or something that can
say, "Check line below Address - if Fax Number, leave alone, if
something else, add Fax Number." I'm simplifying of course, but maybe
there's a way?

Thanks again,

Phil

 

[CyberTaz]

Then you'll just have to add another step to the list - call it 0, because
it would best be done *before* step #1:

Do a Find & Replace for:

Find What: fax number:^p (if there's a space following the : in your data
include it - I didn't use one here.)

Replace With: fax number:X^p (where X represents any placeholder
character or string you want to use - perhaps even the word NONE)

This will add "filler" to the records that don't have Fax numbers & won't
change the records that do. (Once you get the list into Excel you can easily
remove the placeholder data.)

You'll also have to include Fax Number: in step #1.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[philipgalitzine]

Then you'll just have to add another step to the list - call it 0, because
it would best be done *before* step #1:

Do a Find & Replace for:

Find What: fax number:^p (if there's a space following the : in your data
include it - I didn't use one here.)

Replace With: fax number:X^p (where X represents any placeholder
character or string you want to use - perhaps even the word NONE)

This will add "filler" to the records that don't have Fax numbers & won't
change the records that do. (Once you get the list into Excel you can easily
remove the placeholder data.)

You'll also have to include Fax Number: in step #1.

Regards |:>)
Bob Jones
[MVP] Office:Mac

Ah, excellent. Now what if the field itself is missing, ie. no label
for Fax Number in the record at all? Is there a way to insert that, or
do a find and replace for that?

Thanks again, you're saving my life here!

Phil

 

[John McGhie]

In that case, let's assume that "Name:" will always be there and always be
the first field.

Replace that with
^pFiller:
^pFiller:
^pFiller:
^p
^pName:

(I showed them vertically, but they're all one line in the Replace With
box.)

The provides three filler fields and a blank cell at the end of each row.

Then convert your table.

The fully populated records will have three blank fields hanging out to the
right: simply delete those columns to get rid of them all in a single
operation.

Records with missing fields will have one or more filler fields. In some
rare cases where their fields are out of order, you will have to drag the
cells into the correct position.

But you will get a fully-populated and regular table structure which you can
then work with.

Cheers

Ah, excellent. Now what if the field itself is missing, ie. no label
for Fax Number in the record at all? Is there a way to insert that, or
do a find and replace for that?

Thanks again, you're saving my life here!

Phil

--
Don't wait for your answer, click here: http://www.word.mvps.org/

Please reply in the group. Please do NOT email me unless I ask you to.

John McGhie, Consultant Technical Writer
McGhie Information Engineering Pty Ltd
http://jgmcghie.fastmail.com.au/
Sydney, Australia. S33°53'34.20 E151°14'54.50
+61 4 1209 1410, mailto:[email protected]

 

[CyberTaz]

An alternative to John's suggestion - clever as the codger may be - I'd
just deal with untangling what you've got for now and wait until I got the
list into Excel to add any additional fields that aren't already a part of
the data structure. Once there it's nothing more than adding captions to
columns in he list of records.

Regards |:>)
Bob Jones
[MVP] Office:Mac