Removing All Non ASCII Characters and Non Printable characters Function Failing

RicardoS

New Member
Joined
Jul 4, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I have written a clean function that Returns a string with Purely Printable ASCII characters and it work well when I call it with a cell:
Both RemoveNonASCII($B10) & RemoveNonASCII(@$BH:$BH) work.

But the moment I try to pass a string from a Hlookup() Funcion it fails with a #VALUE! Error
This Fails: =RemoveNonASCII(HLOOKUP(BH$5,'$A$1:$CE$5002,$A211,FALSE))
Note: HLOOKUP(BH$5,'$A$1:$CE$5002,$A211,FALSE) Gives a String Value.

The Question is simple Why?? does if fail and how can I fix this? I'm pulling in data from other files so I have to do the Lookup...

Here is the Code:

VBA Code:
Function RemoveNonASCII(ByVal TxtString As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim i As Integer
  Dim IndexChar
  Dim CleanString
  
  'Remove Nonbreaking Space Characters?
  If ConvertNonBreakingSpace Then TxtString = Replace(TxtString, Chr(160), " ")
  
  i = 0
  
  While i < Len(TxtString)
    IndexChar = Mid(TxtString, i + 1, 1)
    
    'If Char Good, add it to the string
    If 31 < Asc(IndexChar) And Asc(IndexChar) < 127 Then
        CleanString = CleanString & IndexChar
    End If
    
    i = i + 1
  Wend
  
  RemoveNonASCII = WorksheetFunction.Trim(CleanString)
  End Function
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
See if this works:
VBA Code:
Function RemoveNonASCII(ByVal TxtString As Variant, Optional ConvertNonBreakingSpace As Boolean = True) As String
    Dim i As Integer
    Dim IndexChar
    Dim CleanString
    Dim ResultStr As String

    If Not IsError(TxtString) Then
        'Remove Nonbreaking Space Characters?
        If ConvertNonBreakingSpace Then TxtString = Replace(TxtString, Chr(160), " ")

        i = 0

        While i < Len(TxtString)
            IndexChar = Mid(TxtString, i + 1, 1)

            'If Char Good, add it to the string
            If 31 < Asc(IndexChar) And Asc(IndexChar) < 127 Then
                CleanString = CleanString & IndexChar
            End If

            i = i + 1
        Wend
        ResultStr = WorksheetFunction.Trim(CleanString)
    Else
        ResultStr = "Error - Not Found"
    End If

    RemoveNonASCII = ResultStr
End Function
 
Upvote 0
See if this works:
VBA Code:
Function RemoveNonASCII(ByVal TxtString As Variant, Optional ConvertNonBreakingSpace As Boolean = True) As String
    Dim i As Integer
    Dim IndexChar
    Dim CleanString
    Dim ResultStr As String

    If Not IsError(TxtString) Then
        'Remove Nonbreaking Space Characters?
        If ConvertNonBreakingSpace Then TxtString = Replace(TxtString, Chr(160), " ")

        i = 0

        While i < Len(TxtString)
            IndexChar = Mid(TxtString, i + 1, 1)

            'If Char Good, add it to the string
            If 31 < Asc(IndexChar) And Asc(IndexChar) < 127 Then
                CleanString = CleanString & IndexChar
            End If

            i = i + 1
        Wend
        ResultStr = WorksheetFunction.Trim(CleanString)
    Else
        ResultStr = "Error - Not Found"
    End If

    RemoveNonASCII = ResultStr
End Function
I have not had a chance to test this. I just worked around the issue by adding an additional Col to pull in the data. then used the dat in that col to clean up the text. That worked.
From your code it looks like we are just looking to see if the underlying data has an error(Which it does not), and if we did provide an error message.
But I do want to test to see if changing the Data type to variant would allow the function to accept the data from the Hlookup directly, instead of having to have a dedicated col for the raw data... Cleaning up the Col..
I would also remove the While loop to a For loop for efficiency...
 
Upvote 0
There is no issue with this kind of formula construction for your RemoveNonASCII UDF:

Excel Formula:
=RemoveNonASCII(HLOOKUP(C1,C2:J2,1,FALSE),FALSE)

...unless HLOOKUP is producing an error. I know you said you are convinced your data has no errors, but the fact that you had to use another column to "clean up" the raw data suggests something is going on there.
 
Upvote 0
Weird:

=RemoveNonASCII(HLOOKUP(C1,C2:J2,1,FALSE),FALSE)

Fails for me...
No worries I'm fine with the workaround.
 
Upvote 0
Update as this worked pritty well it was not removing all of the Unicode chars.

I had to change "Asc(" to "AscW(" to correctly map and remove higher Unicode characters.
There is no issue with this kind of formula construction for your RemoveNonASCII UDF:

Excel Formula:
=RemoveNonASCII(HLOOKUP(C1,C2:J2,1,FALSE),FALSE)

...unless HLOOKUP is producing an error. I know you said you are convinced your data has no errors, but the fact that you had to use another column to "clean up" the raw data suggests something is going on there.
OK I had a chance to test out your recommendations, Here are my results:
I still get the error if I do it directly.

=HLOOKUP(C1,C2:J2,1,FALSE),FALSE) => ✅Yr_Satisfaction_Guaranteed✈
=RemoveNonASCII(HLOOKUP(C1,C2:J2,1,FALSE),FALSE)) => #VALUE!
=RemoveNonASCII(#VALUE!) => Error - Not Found

But bouncing the Data through a separate cell works.
=HLOOKUP(C1,C2:J2,1,FALSE),FALSE) => ✅Yr_Satisfaction_Guaranteed✈
=RemoveNonASCII( "✅Yr_Satisfaction_Guaranteed✈" ) => Yr_Satisfaction_Guaranteed

FYI my internal Formula is:
VBA Code:
=IF(OR($B33="",HLOOKUP(BM$1,'Orders'!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,'Orders'!$A$5:$CO$1005,$A33,FALSE))

Instead of:
VBA Code:
HLOOKUP(C1,C2:J2,1,FALSE),FALSE)

Incase that helps

But thanks for trying!
 
Upvote 0
Match Found, Non-Ascii Removed
Book1
ABBLBMBN
1Lookup Value:ludditeé<- In 'Orders' table
2Formula Result:gestoré
3Remove Non-Ascii:gestor
4
5
10Row IndexB33 Value
333A
Sheet1
Cell Formulas
RangeFormula
BM2BM2=IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE))
BM3BM3=RemoveNonASCII(IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)))
 
Upvote 0
Match not found.
Book1
ABBLBMBN
1Lookup Value:dracula<- Not in 'Orders' table
2Formula Result:#N/A
3Remove Non-Ascii:Error - Not Found
4
5
10Row IndexB33 Value
333A
Sheet1
Cell Formulas
RangeFormula
BM2BM2=IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE))
BM3BM3=RemoveNonASCII(IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)))
 
Upvote 0
Invalid row index
Book1
ABBLBMBN
1Lookup Value:ludditeé<- In 'Orders' table
2Formula Result:#VALUE!
3Remove Non-Ascii:Error - Not Found
4
5
10Row IndexB33 Value
330A<- invalid row index
Sheet1
Cell Formulas
RangeFormula
BM2BM2=IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE))
BM3BM3=RemoveNonASCII(IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)))
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top