Spaces causing error

[smcelv]

Hi guys,

Would appreciate any advice on the following please.

The formula below is functioning reasonably well except for spaces occurring a the end of each returned result.
=VLOOKUP([@Column2],'list of part numbers'!A:C,3,FALSE)
I have to "Find and Replace" the spaces for the data to show correctly.
Is there a way to have Excel remove the spaces?

Thanks for any help you can provide.

 

[MARK858]

Have you tried

=TRIM(VLOOKUP([@Column2],'list of part numbers'!A:C,3,FALSE))

 

[Gerald Higgins]

Presumably these space characters exist in the source data - if you're able to remove them from the source data, that might be a better option.

 

[tyija1995]

I agree, taking them out of the source data would be ideal - I have a macro that trims all cells in a workbook (unless they're formulated cells) - perhaps save a copy and run on the copy first to see if it works fluently:

Sub TrimCells()
Dim cell As Range
Dim nshts As Long
Dim i As Long
nshts = ActiveWorkbook.Sheets.Count
    For i = 1 To nshts
        For Each cell In Sheets(i).UsedRange
            If cell.HasFormula = True Then
                'Do Nothing
            Else
                cell.Value = Application.Trim(cell.Value)
            End If
        Next cell
    Next i
End Sub

 

[smcelv]

Thanks guys.

I just set up a macro to search and find and eliminate any spaces in the range.

Thanks for all you advice.