excel novice question on filling in missing fields/cells

[TimR]

I have an Excel file of about 3000 names, addresses, zip codes, etc. This
is used for a mailer database for addressing. Most of the records have a
first and/or last name field completed. However...as the xls file is
updated...there are occasionally records with no FirstName or LastName
available....only the street address...etc.

Is there a way that I can automatically have Excel fill in a text string if
and when my xls file contains records without a first name and last name.

In other words...if both the FirstName field and the LastName field are
empty in a record (row)...can I have Excel automatically enter a text string
(say "Current Resident") into the blank FirstName field...and how do I go
about this ?

Thanks in advance...

Tim R

 

[Ken Johnson]

Hi TimR,
One way is to copy all the data then paste it somewhere else on the
sheet, but not an ordinary paste, do Paste Special>Paste All then click
the Paste Links button.
Then with the pasted column of Names select the top name and type in
the following formula (I have assumed that the original top name is in
A2)...

=IF(A2="","Current Resident",A2)

Fill this formula down to the bottom of the pasted data to replace the
pasted link formula and you should get the desired result.

Hope this helps

Ken Johnson

 

[TimR]

Thanks ken...Worked good...Now..Is there a way to save the new values in the
copied rows...So I can delete the original rows in order not to confuse my
mailing/address /sorting program ?

Tim

 

[Ken Johnson]

Hi Tim,

1. Select all of the pasted data then copy it.
2. If your original data starts in A2 then click in A2 then go Paste
Special> select "Values" on the Paste Special dialog>OK.

This will get you back to your original data position and previously
blank FirstName cells will now show "Current Resident".

3. You can then delete the copy of your original data that was used to
generate the "Current Resident" entries.

Hope this helps.

Ken Johnson