Replace text to number with conditional formatting

First post - thanks in advance for helping out.

I have a spreadsheet that automatically feeds in values from an external database. I'd like to replace any incoming cells of "#N/A N/A" with "0," and I can't apply any formula to the specific cell because the data import overwrites it with either a numerical value of "#N/A N/A." Please note: this isn't an Excel derived error, but a text-based error message inputted from the database. Excel doesn't recognize it as anything other than a string of arbitrary letters.

I've figured out how to do the opposite: I click conditional formatting, apply new rule, and use a formula to determine which cells to format. I input =A1=0, and the go to custom format and enter [=0]"#N/A N/A" . This works - if I enter a "0," I end up with my text. I'd like to do something like this, but the other way around.

Any ideas?

Best.

Originally Posted by MontrealMTL

I have a spreadsheet that automatically feeds in values from an external database.

How does it "Automatically feed", via formula? Let's say, if that external database is in sheet "Sheet1" of file "Database", you have formula like this:

=[Database.xlsx]Sheet1!A1

where A1 is just any cell you take the data from?

If so, configure the formula this way:

=IF([Database.xlsx]Sheet1!A1="#N/A N/A";0;[Database.xlsx]Sheet1!A1)

That way, if cell content in external database is "#N/A N/A", it will get replaced with zero, otherwise, it will remain unchanged. Please note this works only if "#N/A N/A" is actual cell content in external database, and not caused by calculation error (which I understood is case), and check if argument separator in your Excel is semicolon (as I use) or colon.

Hi Froment, thanks for you reply.

The Excel doc has a formula that pulls from a non-Excel database. Would this still work?

Many thanks.

Further clarification: The formula generates custom data from a subscription database. I enter it in, and the formula links with keywords in columns that tell it which data to find and place underneath the keyword.

Well, then it's beyond my abilities, sorry. I thought both source and destination files were Excel ones...

No worries - thank you for trying.

The only thing I can think of would be to find a way to have conditional formatting replace all #N/A N/A with 0, or have a new sheet with a substitute or IF function copy the data and replace any #N/A N/A with 0. This file will be unimaginably large, however, if I do the latter. I appreciate all cracks at it!

Well, as far as I know, you cannot just replace the content by conditional formatting, you would have to use macros to accomplish that.

As for the idea for another sheet, you're right that it would duplicate the size of the workbook, but I don't see other issues, and in that case I believe my idea above would work - you feed one sheet as usual, and then you add another sheet, just linking the same cells with the formula above, i.e., if raw data is in Sheet1, then in Sheet2, cell A1 reads:

=IF(Sheet1!A1="#N/A N/A";0;Sheet1!A1)

and then copy accross the sheet, as far as you have the data.

Again, "#N/A N/A" would have to be the actual cell content; otherwise, if it's calculation produced formula, use

=IF(ISERROR(Sheet1!A1);0;Sheet1!A1)