Calculated Field Replaces Character, Exclude Errors From Query Results

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
I have Make Table query that evaluates a field, replaces an undesired character ("*") with another ("_"). The replace function within the calculated field works correctly, however errors appear in the results. This creates problems down the line.

Is it possible to somehow exclude the errors from appearing in the query results? Here's a specific example:
Code:
Calc_Field: Replace([Target_Field], "*", "_")

I've tried adding this to the Criteria, without success:
Code:
Not IsError(Replace([Target_Field], "*", "_"))

The errors I see are #Error , and #Name ... The original Target_Field values are all over the place, which is why I'm attempting to clean it up with the Make Table query. I'm ok with simply not including any records that resulted in an error or were blank for example.

Thanks!
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,879
Office Version
  1. 365
Platform
  1. Windows
Can you provide some example values that lead to errors?
 

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
Can you provide some example values that lead to errors?

So it's actually looking like the problem records are NULL values. I'm finding that if I add the source field to the query (by itself) with a criteria "Is Not Null" that it appears to be filtering the list correctly without errors in the calculated field (that replaces * with _).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,879
Office Version
  1. 365
Platform
  1. Windows
That is what I thought might be going on. Yep, filter those out and you should be good.
 

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
That is what I thought might be going on. Yep, filter those out and you should be good.

How might I limit to a distinct list, with the distinct criteria enforced only the calculated field? I have another field in my query, "Area". Testing "SELECT Distinct... ", this certainly cuts down on the list. But it seems to include duplicate records of calculated_field if more than one unique "Area" exists per calculated_field.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,879
Office Version
  1. 365
Platform
  1. Windows
Using DISTINCT, it is going to include all of the fields in your SELECT clause to determine what is a duplicate.
The field that you are using for your Criteria, uncheck the "Show" box under it, and see if that resolves your issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top