Spaces causing error

smcelv

Board Regular
Joined
Jul 26, 2011
Messages
58
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0
Thanks guys.

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

Thanks for all you advice.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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