Filling in Blank Cells
 

Hi. I am using MS Excel 2003, and I am very new to the program, so if you
can help me, please explain everything in very easy English.

I need to fill many blank (empty) cells that are located in various places
(there are five columns) throughout a worksheet. I want to fill the blanks
with an "@" (or some other unique character) so I can later search and
replace to remove them easily (after some sorting). How can this be done?
And please explain it so a newcomer can understand. Thanks.

Try the following...

1) Select/highlight your data (five columns)

2) Edit Go To Special Blanks Ok

3) Enter your unique character

4) Press CONTROL+ENTER

Hope this helps!

In article ,
"Rebecca" wrote:

Hi. I am using MS Excel 2003, and I am very new to the program, so if you
can help me, please explain everything in very easy English.

I need to fill many blank (empty) cells that are located in various places
(there are five columns) throughout a worksheet. I want to fill the blanks
with an "@" (or some other unique character) so I can later search and
replace to remove them easily (after some sorting). How can this be done?
And please explain it so a newcomer can understand. Thanks.

I can only guess at the structure of your spread sheet.

First, I would suggest you add a field (column) "Unique reference number"
(URN) and fill it with a unique number for each record. This will ensure
that, whatever happens, you may be able to re-sort the spreadsheet back into
its original order.

Secondly sort (each field) which will bring all the blank cells together so
that you may add "@" en bloc.

Sort the spreadsheet back to its original order and delete the field URN.

Depending on the structure and size of you spreadsheet this may be a bit
laborious but it should make it easier not to miss a cell or two if you were
to look and change individual cells.

Regards.

Bill Ridgeway
Computer Solutions

"Rebecca" wrote in message
...
Hi. I am using MS Excel 2003, and I am very new to the program, so if you
can help me, please explain everything in very easy English.

I need to fill many blank (empty) cells that are located in various places
(there are five columns) throughout a worksheet. I want to fill the
blanks
with an "@" (or some other unique character) so I can later search and
replace to remove them easily (after some sorting). How can this be done?
And please explain it so a newcomer can understand. Thanks.

Thanks Domenic and Bill for your kind help.

"Bill Ridgeway" wrote:

I can only guess at the structure of your spread sheet.

First, I would suggest you add a field (column) "Unique reference number"
(URN) and fill it with a unique number for each record. This will ensure
that, whatever happens, you may be able to re-sort the spreadsheet back into
its original order.

Secondly sort (each field) which will bring all the blank cells together so
that you may add "@" en bloc.

Sort the spreadsheet back to its original order and delete the field URN.

Depending on the structure and size of you spreadsheet this may be a bit
laborious but it should make it easier not to miss a cell or two if you were
to look and change individual cells.

Regards.

Bill Ridgeway
Computer Solutions

"Rebecca" wrote in message
...
Hi. I am using MS Excel 2003, and I am very new to the program, so if you
can help me, please explain everything in very easy English.

I need to fill many blank (empty) cells that are located in various places
(there are five columns) throughout a worksheet. I want to fill the
blanks
with an "@" (or some other unique character) so I can later search and
replace to remove them easily (after some sorting). How can this be done?
And please explain it so a newcomer can understand. Thanks.

I forgot to ask, Bill, and I'm kind of embarrassed to do so. How do I add a
unique reference number to a column? The worksheet is very long.

"Bill Ridgeway" wrote:

I can only guess at the structure of your spread sheet.

First, I would suggest you add a field (column) "Unique reference number"
(URN) and fill it with a unique number for each record. This will ensure
that, whatever happens, you may be able to re-sort the spreadsheet back into
its original order.

Secondly sort (each field) which will bring all the blank cells together so
that you may add "@" en bloc.

Sort the spreadsheet back to its original order and delete the field URN.

Depending on the structure and size of you spreadsheet this may be a bit
laborious but it should make it easier not to miss a cell or two if you were
to look and change individual cells.

Regards.

Bill Ridgeway
Computer Solutions

"Rebecca" wrote in message
...
Hi. I am using MS Excel 2003, and I am very new to the program, so if you
can help me, please explain everything in very easy English.

I need to fill many blank (empty) cells that are located in various places
(there are five columns) throughout a worksheet. I want to fill the
blanks
with an "@" (or some other unique character) so I can later search and
replace to remove them easily (after some sorting). How can this be done?
And please explain it so a newcomer can understand. Thanks.

Rebecca wrote <<How do I add a unique reference number to a column?

Go to the column past the last one in the spread sheet.

Highlight the column. To do this click on the letter at the head of the
column.

Format the column. To do this click on <Format<Cells<Number<OK

Ensure that "allow cell drag and drop" is enabled. To do this click on
<Tools<Options<Edit and tick on <Allow cell drag and drop

Seed the number. To do this insert 1 & 2 into cells Z1 and Z2 respectively.

Highlight cells Z1 and Z2. To do this left an hold whilst drawing the mouse
over the cells

Extend the numbers over the required range. To do this place the mouse
pointer over the blob at the south-east corner of the cell and drag it down
to the bottom of the range.

All this sounds complicated but that is common to most instruction sets.

Regards.

Bill Ridgeway
Computer Solutions

Why not go with Domenic's solution, no need for sorting and done in less
than 10 seconds?

--
Regards,

Peo Sjoblom


"Rebecca" wrote in message
...
I forgot to ask, Bill, and I'm kind of embarrassed to do so. How do I add
a
unique reference number to a column? The worksheet is very long.

"Bill Ridgeway" wrote:

I can only guess at the structure of your spread sheet.

First, I would suggest you add a field (column) "Unique reference
number"
(URN) and fill it with a unique number for each record. This will ensure
that, whatever happens, you may be able to re-sort the spreadsheet back
into
its original order.

Secondly sort (each field) which will bring all the blank cells together
so
that you may add "@" en bloc.

Sort the spreadsheet back to its original order and delete the field URN.

Depending on the structure and size of you spreadsheet this may be a bit
laborious but it should make it easier not to miss a cell or two if you
were
to look and change individual cells.

Regards.

Bill Ridgeway
Computer Solutions

"Rebecca" wrote in message
...
Hi. I am using MS Excel 2003, and I am very new to the program, so if
you
can help me, please explain everything in very easy English.

I need to fill many blank (empty) cells that are located in various
places
(there are five columns) throughout a worksheet. I want to fill the
blanks
with an "@" (or some other unique character) so I can later search and
replace to remove them easily (after some sorting). How can this be
done?
And please explain it so a newcomer can understand. Thanks.

Peo,

I don't have Domenic's solution (not received or deleted in error) could you
please re-post it?

Thanks.

Regards.

Bill Ridgeway
Computer Solutions

Sure

"Try the following...

1) Select/highlight your data (five columns)

2) Edit Go To Special Blanks Ok

3) Enter your unique character

4) Press CONTROL+ENTER


--
Regards,

Peo Sjoblom


"Bill Ridgeway" wrote in message
...
Peo,

I don't have Domenic's solution (not received or deleted in error) could
you please re-post it?

Thanks.

Regards.

Bill Ridgeway
Computer Solutions

Peo,

Thanks for that. This is better than my suggestion for Rebecca for filling
in blanks.

Regards.

Bill Ridgeway
Computer Solutions

"Peo Sjoblom" wrote in message
...
Sure

"Try the following...

1) Select/highlight your data (five columns)

2) Edit Go To Special Blanks Ok

3) Enter your unique character

4) Press CONTROL+ENTER


--
Regards,

Peo Sjoblom


"Bill Ridgeway" wrote in message
...
Peo,

I don't have Domenic's solution (not received or deleted in error) could
you please re-post it?

Thanks.

Regards.

Bill Ridgeway
Computer Solutions

Personally I'd do both. Anytime I'm going to sort I will always add a
unique field no matter what. Been there too many times when I've not done
it and then regretted it (Have to close without saving and lose what you've
done to get back). I'll use Domenic's method for the blanks, but SORT =
UNIQUE ID every time.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Bill Ridgeway" wrote in message
...
Peo,

Thanks for that. This is better than my suggestion for Rebecca for
filling
in blanks.

Regards.

Bill Ridgeway
Computer Solutions

"Peo Sjoblom" wrote in message
...
Sure

"Try the following...

1) Select/highlight your data (five columns)

2) Edit Go To Special Blanks Ok

3) Enter your unique character

4) Press CONTROL+ENTER


--
Regards,

Peo Sjoblom


"Bill Ridgeway" wrote in message
...
Peo,

I don't have Domenic's solution (not received or deleted in error)
could
you please re-post it?

Thanks.

Regards.

Bill Ridgeway
Computer Solutions

Kan,

Thanks.

You wrote <<Been there too many times when I've not done it and then
regretted it It's nice to know I'm not the only one.

This technique is also useful when merging (large) databases. The URN is
the only stable piece of data on otherwise changing data. It is, therefore,
easier to then sort on this to bring any duplicates together. I usually
then add a further field =IF(a2=a1,1,0) to identify the duplicates and then
copy / paste special / values in that column. It's then easy to Find
records with "1" and delete the appropriate duplicate records.

Regards.

Bill Ridgeway
Computer Solutions

"Ken Wright" wrote in message
...
Personally I'd do both. Anytime I'm going to sort I will always add a
unique field no matter what. Been there too many times when I've not done
it and then regretted it (Have to close without saving and lose what
you've
done to get back). I'll use Domenic's method for the blanks, but SORT =
UNIQUE ID every time.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Bill Ridgeway" wrote in message
...
Peo,

Thanks for that. This is better than my suggestion for Rebecca for
filling
in blanks.

Regards.

Bill Ridgeway
Computer Solutions

"Peo Sjoblom" wrote in message
...
Sure

"Try the following...

1) Select/highlight your data (five columns)

2) Edit Go To Special Blanks Ok

3) Enter your unique character

4) Press CONTROL+ENTER


--
Regards,

Peo Sjoblom


"Bill Ridgeway" wrote in message
...
Peo,

I don't have Domenic's solution (not received or deleted in error)
could
you please re-post it?

Thanks.

Regards.

Bill Ridgeway
Computer Solutions