Removing invisible characters from VBA array and dynamic formula

Spencer500

New Member
Joined
Dec 18, 2022
Messages
9
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:


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.
 

Attachments

  • Excel Invisible Character Question Screenshot.JPG
    Excel Invisible Character Question Screenshot.JPG
    23.7 KB · Views: 20
Last edited by a moderator:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
The first thing you should do is determine what invisible character you are dealing with.

Book1
ABCDEF
1
2Char Code
31400140049
42#N/A 50050
53#N/A 60051
6 5004800160
75900590053
8#N/A61000<empty cell>
Sheet3
Cell Formulas
RangeFormula
B3:B8B3=VLOOKUP(A3,$C$3:$D$8,2,FALSE)
F3:F8F3=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.
 
Upvote 0
@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. 😊
 
Upvote 0
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)

VBA Code:
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
 
Upvote 0
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
ABCDEFGHIJ
31400140049494949
42#N/A50050505050
53#N/A60051515151
65004800<empty cell>#VALUE!#VALUE!#VALUE!
75900590053535353
BlankArray
Cell Formulas
RangeFormula
G3:G7G3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
H3:H7H3=CODE(LEFT(TRIM(A3),1))
I3:I7I3=CODE(TRIM(A3))
J3:J7J3=CODE(A3)
B3:B7B3=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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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:

Cell Formulas
RangeFormula
G3:G7G3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
H3:H7H3=CODE(LEFT(TRIM(A3),1))
I3:I7I3=CODE(TRIM(A3))
J3:J7J3=CODE(A3)
K3:K7K3=IF(LEN(A3)>0,CODE(RIGHT(TRIM(A3),1)),"<empty cell>")
L3:L7L3=CODE(RIGHT(TRIM(A3),1))
B3:B7B3=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.
 
Upvote 0
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.

VBA Code:
    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
 
Upvote 0
Cell Formulas
RangeFormula
G3:G7G3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
H3:H7H3=CODE(LEFT(TRIM(A3),1))
I3:I7I3=CODE(TRIM(A3))
J3:J7J3=CODE(A3)
K3:K7K3=IF(LEN(A3)>0,CODE(RIGHT(TRIM(A3),1)),"<empty cell>")
L3:L7L3=CODE(RIGHT(TRIM(A3),1))
B3:B7B3=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
ABCDEFG
1
2Lookup Char CodeTable Char Code
3140014004949
42#N/A50050<empty cell>
53#N/A60051<empty cell>
6#N/A4800<empty cell>52
7590059005353
Sheet1
Cell Formulas
RangeFormula
F3:F7F3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty cell>")
G3:G7G3=IF(LEN(C3)>0,CODE(LEFT(TRIM(C3),1)),"<empty cell>")
B3:B7B3=IF(LEN(A3)=0,"#N/A",(VLOOKUP(A3,$C$3:$D$7,2,FALSE)))
 
Upvote 0
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
ABCD
1
2
314001400
42#N/A500
53#N/A600
65004800
759005900
8
90
10TRUE
Main
Cell Formulas
RangeFormula
B3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)
A9A9=LEN(A6)
A10A10=ISTEXT(A6)


With that sheet active, try running this code.
VBA 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
ABCD
1
2
314001400
42#N/A500
53#N/A600
6#N/A4800
759005900
8
90
10FALSE
Main
Cell Formulas
RangeFormula
B3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)
A9A9=LEN(A6)
A10A10=ISTEXT(A6)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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