Removing All Non ASCII Characters and Non Printable characters Function Failing

[RicardoS]

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:

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

 

[rlv01]

See if this works:

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

 

[RicardoS]

See if this works:

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...

 

[rlv01]

There is no issue with this kind of formula construction for your RemoveNonASCII UDF:

=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.

 

[RicardoS]

Weird:

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

Fails for me...
No worries I'm fine with the workaround.

 

[RicardoS]

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:

=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:

=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:

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

Incase that helps

But thanks for trying!

 

[rlv01]

Match Found, Non-Ascii Removed

Book1
	A	B																																																														BL	BM	BN
1																																																																Lookup Value:	ludditeé	<- In 'Orders' table
2																																																																Formula Result:	gestoré
3																																																																Remove Non-Ascii:	gestor
4
5
10	Row Index	B33 Value
33	3	A
Sheet1

Cell Formulas
Range	Formula
BM2	BM2	=IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE))
BM3	BM3	=RemoveNonASCII(IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)))

 

[rlv01]

Match not found.

Book1
	A	B																																																														BL	BM	BN
1																																																																Lookup Value:	dracula	<- Not in 'Orders' table
2																																																																Formula Result:	#N/A
3																																																																Remove Non-Ascii:	Error - Not Found
4
5
10	Row Index	B33 Value
33	3	A
Sheet1

Cell Formulas
Range	Formula
BM2	BM2	=IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE))
BM3	BM3	=RemoveNonASCII(IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)))

 

[rlv01]

Invalid row index

Book1
	A	B																																																														BL	BM	BN
1																																																																Lookup Value:	ludditeé	<- In 'Orders' table
2																																																																Formula Result:	#VALUE!
3																																																																Remove Non-Ascii:	Error - Not Found
4
5
10	Row Index	B33 Value
33	0	A																																																														<- invalid row index
Sheet1

Cell Formulas
Range	Formula
BM2	BM2	=IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE))
BM3	BM3	=RemoveNonASCII(IF(OR($B33="",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)=""),"",HLOOKUP(BM$1,Orders!$A$5:$CO$1005,$A33,FALSE)))