Removing invisible characters from VBA array and dynamic formula

[Spencer500]

Hi,

I am building a VBA macro that will run VLOOKUP on user input with the following functionality:

1. Takes in user typed or pasted data from sheet "Main"
2. Ports the data from sheet "Main" to an array.
3. Array processing: Common invisible characters are removed, data is trimmed, data is casted to text to ensure the values to look up and the look-up data set are the same data type to ensure VLOOKUP will work.
4. The array then moves the data to sheet "BlankArray", where a VBA-based dynamic VLOOKUP is run on the data.

The problem is that invisible characters are somehow being created by this process, which is causing the VLOOKUP to return 500 in cell B6 from cell D4 because a match between A6 and C4 was identified by VLOOKUP. But the respective cells from sheet "Main" from which the data originated are blank.

When I select cell A6, then hit Delete, the invisible character is then deleted, which then causes VLOOKUP to return #NA in cell B6, which is what should happen if the invisible character were not in cell A6.

Invisible characters are also in cells C4 and C5. If I do not delete cell A6, and if I, instead, select C4, then hit delete, VLOOKUP returns 600 in cell B6 from D5 due to the next invisible character match between C5 and A6.

How can I modify this process to remove the invisible characters from appearing in the cells that should be blank? Also, are these invisible characters also appended to the cells that have data? If so, then how can I ensure the invisible characters are not appended to the cells with data? Do you have any other suggestions for improvements?

Please find attached workbook screenshot.

VBA Code:

Public InitialArray As Variant

Sub Match()

Sheets("Main").Select

Dim i As Long
Dim MaxFinalRow As Long


Y1 = 1
Y2 = 1
i = 1

Dim FinalRowValue As Long
Dim FinalRowKey As Long

FinalRowValue = Cells(Rows.Count, 1).End(xlUp).Row

FinalRowKey = Cells(Rows.Count, 3).End(xlUp).Row

FinalRowValueToReturn = Cells(Rows.Count, 4).End(xlUp).Row


If FinalRowValue > FinalRowKey Then
    MaxFinalRow = FinalRowValue
Else
    MaxFinalRow = FinalRowKey
End If


InitialArray = Range(Cells(3, 1), Cells(MaxFinalRow, 4))

Sheets("BlankArray").Select

Range("A1:F13").Select
Selection.ClearContents

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(160), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(10), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(13), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(26), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(3), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(28), vbNullString)
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(160), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(10), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(13), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(26), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(3), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(28), vbNullString)
    Next i

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Trim(InitialArray(i, 1))
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Trim(InitialArray(i, 3))
    Next i


For i = 1 To (FinalRowValue - 2)
    InitialArray(i, 1) = CStr(InitialArray(i, 1))
Next i


For i = 1 To (FinalRowKey - 2)
    InitialArray(i, 3) = CStr(InitialArray(i, 3))
Next i

Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray


    Range("B3").Formula = "=VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE)"
    Range("B3").Select
    Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(FinalRowValue, 2)), Type:=xlFillDefault

End Sub

Thanks.

 

[rlv01]

The first thing you should do is determine what invisible character you are dealing with.

Book1
	A	B	C	D	E	F
1
2						Char Code
3	1	400	1	400		49
4	2	#N/A		500		50
5	3	#N/A		600		51
6		500	4	800		160
7	5	900	5	900		53
8		#N/A	6	1000		<empty cell>
Sheet3

Cell Formulas
Range	Formula
B3:B8	B3	=VLOOKUP(A3,$C$3:$D$8,2,FALSE)
F3:F8	F3	=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

as it makes the code easier to read.)

Also , instead of posting data as a screen graphic, you might try this.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Peter_SSs]

@Spencer500
Welcome to the MrExcel board!

As requested already by @rlv01, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details (same link as provided by rlv01). I have added the tags for you this time.

 

[Alex Blakenburg]

If you think you have identified all the characters that you are likely to need replaced, you can clean up the whole Data Area without looping,
See if this gives you the same or better result.
( Ideally you also want to get rid of the Select statements and Explicitly refer to the workhsheets)

Sub CleanData()

Dim i As Long
Dim MaxFinalRow As Long
Dim InitialArray
Dim Y1 As Long, Y2 As Long

Y1 = 1
Y2 = 1
i = 1

Dim FinalRowValue As Long
Dim FinalRowKey As Long
Dim FinalRowValueToReturn As Long

FinalRowValue = Cells(Rows.Count, 1).End(xlUp).Row

FinalRowKey = Cells(Rows.Count, 3).End(xlUp).Row

FinalRowValueToReturn = Cells(Rows.Count, 4).End(xlUp).Row

If FinalRowValue > FinalRowKey Then
    MaxFinalRow = FinalRowValue
Else
    MaxFinalRow = FinalRowKey
End If

InitialArray = Range(Cells(3, 1), Cells(MaxFinalRow, 4))

Sheets("BlankArray").Select

Range("A1:F13").Select
Selection.ClearContents
    
    InitialArray = Application.Substitute(InitialArray, Chr(160), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(10), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(13), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(26), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(3), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(28), vbNullString)
    
    InitialArray = Application.Trim(InitialArray)

Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray

    Range("B3").Formula = "=VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE)"
    Range("B3").Select
    Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(FinalRowValue, 2)), Type:=xlFillDefault

End Sub

 

[Spencer500]

Hi @rlv01 and @Alex Blakenburg ,

Thank you for your replies.

Using Office 365, I have modified my workbook according to your recommendations and the invalid characters have not been identified and have not been eliminated in the results.

--

rlv01:
I tried using the original version of your formula as well as three variations of it in an attempt to identify the invisible character in the results as follows:

Match2.xlsm
	A	B	C	D	E	F	G	H	I	J
3	1	400	1	400			49	49	49	49
4	2	#N/A		500			50	50	50	50
5	3	#N/A		600			51	51	51	51
6		500	4	800			<empty cell>	#VALUE!	#VALUE!	#VALUE!
7	5	900	5	900			53	53	53	53
BlankArray

Cell Formulas
Range	Formula
G3:G7	G3	=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
H3:H7	H3	=CODE(LEFT(TRIM(A3),1))
I3:I7	I3	=CODE(TRIM(A3))
J3:J7	J3	=CODE(A3)
B3:B7	B3	=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

As you can see from the above embedded table, your original formula to identify the invisible character and the three variations of it did not return Char code 160 in any of the row 6 columns. Did you type in "160" in the embedded table in your reply or was it returned by your formula?

--

Alex Blakenburg:
Thanks for the tip that I do not need to traverse the array.

The invisible characters in cells A6, C4, and C5 are still returned in the results after running your CleanData Subroutine.
The embedded table contains the results after running your CleanData Subroutine.

In your CleanData Subroutine you used the Substitute function to eliminate the invisible character codes instead of the Replace function as I did in my post.
What is the difference between using the Replace function vs. using the Substitute function when manipulating data in VBA arrays?
I noticed that you did not declare InitalArray as a Variant. What is the advantage of not using the Variant data type for InitialArray in this macro?

--

Do you have any other suggestions for how to identify the character code(s) for the invisible characters or to eliminate the invisible characters so that they do not appear in the results?

Thanks.

 

[Alex Blakenburg]

In your CleanData Subroutine you used the Substitute function to eliminate the invisible character codes instead of the Replace function as I did in my post.
What is the difference between using the Replace function vs. using the Substitute function when manipulating data in VBA arrays?
I noticed that you did not declare InitalArray as a Variant. What is the advantage of not using the Variant data type for InitialArray in this macro?

• Application.Trim & Application.Substitute will work on the entire array without looping, there are only a few functions that will do that.
• InitialArray - I moved it from a public variable to a module variable and simply forgot to add the "as Variant", as it happens it is the default but I would normally spell it out.

The Clean is not going to do anything if the issue is not one of the specified characters.

Most of the code formulas being tried are assuming that the character is on the left. If you use Len on the field does the length seem consistent with what you are seeing in the cell ?
Try getting the Right character and seeing what the code is on that.

 

[Spencer500]

Hi Alex,

Thanks for pointing out those useful features of the Trim and Substitute functions.

I have modified the formulas to take the character from the right per your recommendation (see highlighted columns on far right). But the results are the same as those returned from the other formulas. Grid from modified workbook:

Match3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
3	1	400	1	400			49	49	49	49	49	49
4	2	#N/A		500			50	50	50	50	50	50
5	3	#N/A		600			51	51	51	51	51	51
6		500	4	800			<empty cell>	#VALUE!	#VALUE!	#VALUE!	<empty cell>	#VALUE!
7	5	900	5	900			53	53	53	53	53	53
BlankArray

Cell Formulas
Range	Formula
G3:G7	G3	=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
H3:H7	H3	=CODE(LEFT(TRIM(A3),1))
I3:I7	I3	=CODE(TRIM(A3))
J3:J7	J3	=CODE(A3)
K3:K7	K3	=IF(LEN(A3)>0,CODE(RIGHT(TRIM(A3),1)),"<empty cell>")
L3:L7	L3	=CODE(RIGHT(TRIM(A3),1))
B3:B7	B3	=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

Do you have any other recommendations for how to identify the character code for the invisible characters?
If it is not possible to use VBA or formulas to identify the character code for some invisible characters, then do you have any educated guesses on what the character code for the invisible character could be so that it can be removed by the array-based data cleansing process?

Thanks.

 

[Alex Blakenburg]

I am probably running out of ideas. Can you try adding this to the end of your code and see if it makes any difference.

    Dim rng As Range
    Set rng = Range(Cells(3, 1), Cells(FinalRowValue, 4)).Columns(1)
    rng.TextToColumns Destination:=rng.Cells(1, 1), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 1), TrailingMinusNumbers:=True
                
    Set rng = Range(Cells(3, 1), Cells(FinalRowValue, 4)).Columns(3)
    rng.TextToColumns Destination:=rng.Cells(1, 1), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 1), TrailingMinusNumbers:=True

 

[rlv01]

Match3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
3	1	400	1	400			49	49	49	49	49	49
4	2	#N/A		500			50	50	50	50	50	50
5	3	#N/A		600			51	51	51	51	51	51
6		500	4	800			<empty cell>	#VALUE!	#VALUE!	#VALUE!	<empty cell>	#VALUE!
7	5	900	5	900			53	53	53	53	53	53
BlankArray

Cell Formulas
Range	Formula
G3:G7	G3	=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
H3:H7	H3	=CODE(LEFT(TRIM(A3),1))
I3:I7	I3	=CODE(TRIM(A3))
J3:J7	J3	=CODE(A3)
K3:K7	K3	=IF(LEN(A3)>0,CODE(RIGHT(TRIM(A3),1)),"<empty cell>")
L3:L7	L3	=CODE(RIGHT(TRIM(A3),1))
B3:B7	B3	=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

The formula in cell G6 (above) is telling you that there are no hidden characters in cell A6. If there were, =Len(A6) would return a value greater than zero. It appears that VLookup is returning 500 because A6 is empty and C4 it is the first empty cell in the lookup range. To confirm, put a value (any value) in cell C4 (above) and see if B6 changes to 600. Not sure why it is doing that. Vlookup does not behave that way for me, but it does suggest a workaround strategy of including a cell length test as part of the formula:

Book1
	A	B	C	D	E	F	G
1
2						Lookup Char Code	Table Char Code
3	1	400	1	400		49	49
4	2	#N/A		500		50	<empty cell>
5	3	#N/A		600		51	<empty cell>
6		#N/A	4	800		<empty cell>	52
7	5	900	5	900		53	53
Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
G3:G7	G3	=IF(LEN(C3)>0,CODE(LEFT(TRIM(C3),1)),"<empty cell>")
B3:B7	B3	=IF(LEN(A3)=0,"#N/A",(VLOOKUP(A3,$C$3:$D$7,2,FALSE)))

 

[Peter_SSs]

The formula in cell G6 (above) is telling you that there are no hidden characters in cell A6.

.. unless it is a zero-length string.

I suspect that cells A6, C4 & C5 originally contained formulas that returned null strings ""
Subsequently most likely a copy/paste values has occurred. This still leaves those cells with a zero length string.
See my sheet below & results in A9:A10 (& the VLOOKUP in B6)

Spencer500.xlsm
	A	B	C	D
1
2
3	1	400	1	400
4	2	#N/A		500
5	3	#N/A		600
6		500	4	800
7	5	900	5	900
8
9	0
10	TRUE
Main

Cell Formulas
Range	Formula
B3:B7	B3	=VLOOKUP(A3,$C$3:$D$7,2,FALSE)
A9	A9	=LEN(A6)
A10	A10	=ISTEXT(A6)

With that sheet active, try running this code.

Sub test()
  With Range("A3:A7")
    .Value = .Value
  End With
  With Range("C3:C7")
    .Value = .Value
  End With
End Sub

Having done that my sheet now looks like this. Note changes to A10 and B6.

Spencer500.xlsm
	A	B	C	D
1
2
3	1	400	1	400
4	2	#N/A		500
5	3	#N/A		600
6		#N/A	4	800
7	5	900	5	900
8
9	0
10	FALSE
Main

Cell Formulas
Range	Formula
B3:B7	B3	=VLOOKUP(A3,$C$3:$D$7,2,FALSE)
A9	A9	=LEN(A6)
A10	A10	=ISTEXT(A6)