Range Object adds extra space that cannot be trimmed without specifically trimming the cell

[bellg]

I have some code that is supposed to Trim the values of cells quickly, taken from here:
https://www.thespreadsheetguru.com/the-code-vault/a-fast-way-to-clean-trim-cell-values-with-vba-code

<code>

With ActiveWorkbook.Worksheets(WSname)
    .Activate
    
    Dim rng As Object
    Dim Area As Object
    
    'Weed out any formulas from selection
        Set rng = Range(Cells(1, 1), Cells(wsNameallRows, wsNameallColumns)).SpecialCells(xlCellTypeConstants)
    
    'Trim and Clean cell values
      For Each Area In rng.Areas
        Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))") 'this only deletes leading and trailing spaces
      Next Area
End With
</code>

However, I see this happening when I debug the program, and I don't know why:


<code>

With ActiveWorkbook
    Debug.Print varPrdOrd
    Debug.Print PrdOrd
    Debug.Print .Worksheets("Sheet1").Cells(1, 1)
    Debug.Print .Worksheets("Sheet1").Cells(1, 1).Text
    Debug.Print Len(.Worksheets("Sheet1").Cells(1, 1))
    Debug.Print Len(.Worksheets("Sheet1").Cells(1, 1).Text)
End With
</code>

that returns this in the Immediate Window:

<code>

20730642
20730642
 20730642 
20730642
 8 
 8 
</code>

Why does the Debug.Print .Worksheets("Sheet1").Cells(1, 1) return an extra space at the beginning of the value? This is preventing me from using Application.Match properly in some later code. I know can Trim the cell individually and it removes this space, but that would significantly slow down my code if I had to loop through individual cells.

 

[Yongle]

I entered raw values in the immediate window - results below
?20730645
: 20730645
?cstr(20730642)
:20730642

As you can see - it is a formatting difference between Numerics and text in Debug.Print NOT a mystery space.
Your 2 Len formulas are telling you that too.

This is preventing me from using Application.Match properly in some later code

- I think your problem has a different cause

Please provide the following information for a match that is failing
- value being searched for
- the sheet name and cell reference that you know it should find (from a visual inspection)
- the pasted value from that cell
- the line of code which includes the failing MATCH function
- the value that you expect the function to return against that match

thanks

 

[bellg]

Here's a complete subroutine that replicates the problem. Row 1444 Column B has the value "20730645" in my data set.

Sub TestSubMatchError()


With ActiveWorkbook


    Dim PrdOrd As String, varPrdOrd As Variant, rngPrdOrd As Range
    Dim Rng As Range
    
    PrdOrd = "20730642"
    varPrdOrd = CVar(PrdOrd)
    
    With .Worksheets("Sheet1")
        Set rngPrdOrd = .Range(.Cells(4, 2), .Cells(4075, 2))
        .Range(.Cells(4, 2), .Cells(4075, 2)).NumberFormat = "@"
        .Range(.Cells(4, 2), .Cells(4075, 2)).Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 'shouldn't need this from the earlier Trim of the data
    End With
    
    For Each Rng In rngPrdOrd
        If Rng.Row < 1500 And Rng.Row > 1400 Then Debug.Print Rng.Address & "_" & Rng
    Next
        
    Debug.Print varPrdOrd
    Debug.Print PrdOrd
    Debug.Print .Worksheets("Sheet1").Cells(1444, 2)
    Debug.Print .Worksheets("Sheet1").Cells(1444, 2).Text
    Debug.Print Len(.Worksheets("Sheet1").Cells(1444, 2))
    Debug.Print Len(.Worksheets("Sheet1").Cells(1444, 2).Text)
    
    If Not IsError(Application.Match(varPrdOrd, rngPrdOrd, 0)) Then
        j = Application.Match(varPrdOrd, rngPrdOrd, 0)
    Else
        Exit Sub
    End If
End With


End Sub

 

[bellg]

I found the cause of the error.

Steps:
I import a column of data into Worksheet("TEMPORARY")
I format that column of data as "Text" in Worksheet("TEMPORARY")
I format that same data range as "Text" in Worksheet("Sheet1")
I set the data range in Worksheet("Sheet1") equal to the same data range in Worksheet("TEMPORARY"), to transfer the data
I then want to look up the row with number "20730642" in Worksheet("Sheet1")

This gives me an error because it still thinks the cell with the value "20730642" is a Number and not Text. How can I easily fix that?

 

[Yongle]

To "cheat" to make the match work you could try converting the value being searched for inside the MATCH (although missing leading zeros can cause problems)

If the value being matched is a number but the range of values holds value as text, try:
Application.Match(CStr(NUMBER)), Range(TEXT VALUES), 0)

If the value being matched is held as text but the range of values consists of numbers, try
Application.Match(CLng(TEXT)), Range(NUMBERS), 0)

Proper fix
It would be better to fix the problem at source
How are you transferring the values?

If the original range is formatted as text, copy and paste should do the trick. New range should take its format from original range

rng1.Copy rng2

Another way is to format the new range as text before pasting values

rng2.NumberFormat = "@"
rng1.Copy
rng2.PasteSpecial xlPasteValues

 

[bellg]

Your CLng methodworked for me, since my range held numbers (even though they were formatted astext):
Application.Match(CLng(TEXT)), Range(NUMBERS), 0)

I also devised another solution where I use theTEXT() formula, auto-filled for the entire range, to convert the values totext. I then replace the original column with those formula values.

Source process:
1. I have code that does this for every data pull:
- pull data from SAPinto a TEMPORARY text file
- open that text filewith a TEMPORARY Excel file
- convert the column oftext into columns of delimited data
- delete all data in aTEMPORARY worksheet in my main workbook
- copy the data from theTEMPORARY Excel file into the TEMPORARY worksheet in my main workbook
- close the TEMPORARYExcel file
2. Then, I transfer the TEMPORARYworksheet data in my main workbook to other worksheets in the same file,depending on which data I had pulled from SAP. When I transfer the data fromthe temporary excel file to my temporary worksheet, the same data can populatein different columns depending on the particular data pull.

When I format both the TEMPORARY worksheet AND theworksheet I'm transferring data to as Text, the range values still identify asNumbers when I use MATCH. It doesn't make it easy.

 

[Yongle]

When I format both the TEMPORARY worksheet AND theworksheet I'm transferring data to as Text, the range values still identify asNumbers when I use MATCH. It doesn't make it easy.

Test this snippet of code on (a copy of!!) a typical column of numerical data that is giving you the problem
- select the range and run the macro

Sub ConvertToText()
    Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)
End Sub

 

[bellg]

That worked!
Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)

This is definitely the simplest solution I've seen for converting Numbers to Text. Thank you for your time and for sharing.

 

[Yongle]

Thanks for the feedback