Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy down - special to fill only the blank cells | Excel Discussion (Misc queries) | |||
blank cells | Excel Discussion (Misc queries) | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Non Blank - Blank Cells???? | Excel Discussion (Misc queries) | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) |