Filling in Blank Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
Thanks Domenic and Bill for your kind help.

Bill Ridgeway said:
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
 
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.
 
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?
 
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
 
Peo,

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

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.
 
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
 
Back
Top