Replace empty cells with text in a text string

1815
9
Jump to solution
05-31-2018 04:25 AM
TheodoreF
Occasional Contributor II

I can't find the answer anywhere, but how can I replace cells in a string field that have no data at all in them, with text? I'm not looking to replace spaces.

The second row here in Claim_Spend_in_£ (which is a text tield), has no data at all in it. It is not a blank space, or a NULL. I want all blank entries to be replaced with the text "Unrecorded". But the code I used above does nothing... I've tried using "", " ", None and neither of those work. I've also tried .strip to strip the field of any spaces first before running replace. No difference.

How can I acheive this?

And for further help, how can I replace all <Null> entries in a DATE formatted field, with the date "11/11/1111"? (Essentially I can't have any null values, so any unrecorded dates have to be a made up filler of 11/11/1111...

I see there is no .replace function for non-string fields...

0 Kudos
1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Esteemed Contributor

Try this... assuming it is some variant of a space or whitespace this field calculator expression should work

or it that fails you might try a variant

View solution in original post

9 Replies
DanPatterson_Retired
MVP Esteemed Contributor

Try this... assuming it is some variant of a space or whitespace this field calculator expression should work

or it that fails you might try a variant

TheodoreF
Occasional Contributor II

The first solution gave ERROR 999999:

and the second solution did the same:

Thanks for the help though! I'm going to try selecting all blank entries and then insert "Unrecorded" into those selected.

EDIT: that won't work as I need this process to run outside of an ArcGIS Pro session. Select by attribute works off ToC layers...

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

You indented the return line.

Don't

see my example

TheodoreF
Occasional Contributor II

Ok interestingly my original code works with a few minor tweaks- swapping " " for ' '

hmmmm...

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

The single quote issue is likely related to SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop :

Common queries: Searching strings

Strings must always be enclosed in single quotation marks in queries, for example:

STATE_NAME = 'California'
DanPatterson_Retired
MVP Esteemed Contributor

As for the <null>, simply do a query for null to get them selected, then when you use the Field calculator, you just enter your value for the field calculation.  If it is an actual date field, I am not sure you just put in your 11/11/1111 string, but report back if you have an issue

DanPatterson_Retired
MVP Esteemed Contributor

'return' should use a single indent, you have double indented, hence it will fail

TheodoreF
Occasional Contributor II

Thanks Dan, both your solutions worked perfectly with the single indent. Interestingly when typing out your script it defautled to double indent when I hit Enter after the line ending "Unrecorded". I assumed Pro would handle indents for me.

0 Kudos
TheodoreF
Occasional Contributor II

To answer the second part of my question relating to replacing all NULL values in a date field, with the made up date of '11/11/1111', this solution works well: