Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level III

## Filling empty cells in a specific “character” column

Hello,

I need help with filling empty cells in a specific “character” column.

I was able to find a script that does it to all numeric cells of the entire table, but I need it to edit a specific text column.

I have something like the table below and I want to fill the empty cells in the "United" column with an expression like "PASS".

so from this table:

 Rev OB United ABC123 1 CB1 ABC321 CB5 CBA213 4 ABC546 CB1 ABC654 5 CBA213 6 CB1 ABC453 CB5 ABC321 8 CBA213 8

I will get:

 Rev OB United ABC123 1 CB1 ABC321 CB5 CBA213 4 PASS ABC546 CB1 ABC654 5 PASS CBA213 6 CB1 ABC453 CB5 ABC321 8 PASS CBA213 8 PASS

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User

## Re: Filling empty cells in a specific “character” column

``````names default to here(1);
dt=current data table();
for each row(
If(:United=="", :United="PASS");
);``````

or to do it interactively,

Type into one blank cell for column United, "PASS"

Then click on that cell and copy it to the paste buffer (Cntl/C)

Then right click on a one of the other blank cells for the column United.

Select the option, "Select Matching Cells"

All blank cells for column United will be selected

Hover over one of the selected cells, and paste into the cell the value from the paste buffer.

All selected cells will now have the word "PASS" pasted into them

Jim
Highlighted
Community Manager

## Re: Filling empty cells in a specific “character” column

The fastest way to do this is to type the word "PASS" into one of the cells. Then select it and use Edit->Copy to copy it to your clipboard.

Then right-click on one of the empty cells and choose Select All Matching Cells and then choose Edit->Paste to paste PASS into all the cells.

If you want to do this programmatically let us know and I'm sure we can point you in that direction.

-Jeff
8 REPLIES 8
Highlighted
Super User

## Re: Filling empty cells in a specific “character” column

``````names default to here(1);
dt=current data table();
for each row(
If(:United=="", :United="PASS");
);``````

or to do it interactively,

Type into one blank cell for column United, "PASS"

Then click on that cell and copy it to the paste buffer (Cntl/C)

Then right click on a one of the other blank cells for the column United.

Select the option, "Select Matching Cells"

All blank cells for column United will be selected

Hover over one of the selected cells, and paste into the cell the value from the paste buffer.

All selected cells will now have the word "PASS" pasted into them

Jim
Highlighted
Level III

## Re: Filling empty cells in a specific “character” column

Thank you all!

What a quick respond!

I want to use a JSL to do it.

When I use that part of the script, it is not working for me:

``````Names Default To Here( 1 );
dt = Current Data Table();
For Each Row( If( United/*###*/ == "", United = "PASS" ) );
Name Unresolved: United{1} in access or evaluation of 'United' , United/*###*/``````
Highlighted
Super User

## Re: Filling empty cells in a specific “character” column

I should have specified a ":" in front of the reference to the column United......... :United

An even more definative reference is dt:United

Make those changes and it should work

Jim
Highlighted
Community Manager

## Re: Filling empty cells in a specific “character” column

The fastest way to do this is to type the word "PASS" into one of the cells. Then select it and use Edit->Copy to copy it to your clipboard.

Then right-click on one of the empty cells and choose Select All Matching Cells and then choose Edit->Paste to paste PASS into all the cells.

If you want to do this programmatically let us know and I'm sure we can point you in that direction.

-Jeff
Highlighted
Community Manager

## Re: Filling empty cells in a specific “character” column

@txnelson always beats me!

-Jeff
Highlighted
Level II

## Re: Filling empty cells in a specific “character” column

Here's a variation on the original request: what if you want to add different values into the empty cells. For example you have the table below, and you want to put C in the third cell, A in the fifth cell, and D in the 8th cell. is there a way to that programmatically? I had initially thought that an easy way to do it would be to add rows and fill them in with all the known letters to ensure we had all of them represented.

 33 A 12 B 15 1 D 7 22 B 9 C 28
Highlighted
Super User

## Re: Filling empty cells in a specific “character” column

There are several ways to do this.  Here is one way:

``````Names Default To Here( 1 );
dt = New Table( "example",
New Column( "Value",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [33, 12, 15, 1, 7, 22, 9, 28] )
),
New Column( "Letter",
Character( 16 ),
"Nominal",
Set Values( {"A", "B", "", "D", "", "B", "C", ""} )
)
);

Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

For( i = 1, i <= N Rows( dt ), i++,
If( :letter[i] == "",
:letter[i] = Substr( Alphabet, i, 1 )
)
);``````
Jim
Highlighted
Level II

## Re: Filling empty cells in a specific “character” column

The code works great! Thanks for the help

Article Labels

There are no labels assigned to this post.