CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
user8517443
user8517443

Reputation:

Determine if there is a space in a cell in Excel

I have a a spreadsheet with over 2000 lines that each have a unique reference number. I want to use the reference numbers to VLOOKUP to another report. my problem is that while all reference numbers have a letter at the end, some have a space between the letter and the numbers while others do not have a space in-between.

For example: 637408 A compared to 837408A

The sheet I want to use a VLOOKUP to compare the references with does not have a space between the letter and the numbers. Is there an Excel function that I can use to quickly detect which references have a space, and if they do, eliminate the space? Thank You.

Upvotes: 0

Views: 1603

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

To remove all spaces in, say, column A, try this short macro:

Sub SpaceKiller()
    Range("A:A").Replace What:=" ", replacement:=""
End Sub

Upvotes: 1

Related Questions