Excel "compare text string" help

  AragornUK 13:24 11 Feb 2006
Locked

Hello all. It's been agood while since I was here, but I decided to dip in again, see who's about. So, any Excel gurus in today?

I have 2 worksheets (for the example, called S1 and S2). I have a list of names on S1 and require a formula that, when I type a name in S2 it will check column A in S1 and highlight if the name is a duplicate. So far so good. I managed this quite easily.

However, the person I'm working on this for informed me that the people using the spreadsheet aren't necessarily the best typers, so there could be leading/trailing spaces in EITHER list. I've managed to incorporate TRIM into the function to shed excess spaces in the list on S2, but trying to put TRIM into the VLOOKUP doesn't seem to be working.

Is there any way of allowing for these extra spaces in the original list, or even easily removing them at time of text entry?

The function I have now is this:

=IF(ISNA(VLOOKUP(TRIM(A4),S1!$A$1:$A$600,1,FALSE)),"","D")

  VoG II 13:59 11 Feb 2006

The reason it won't work is because TRIM is only working on the value to be looked up, not the range that is being looked up.

You could strip the spaces using the following macro

Sub tst()
Dim iRow, txt As String
For iRow = 1 To 600
txt = Sheets("S1").Cells(iRow, 1).Value
Sheets("S1").Cells(iRow, 1).Value = Trim(txt)
Next iRow
End Sub


Then to prevent further entries with spaces, right click the sheet tab, select View Code and enter the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Trim(Target.Value)
End Sub

  AragornUK 14:17 11 Feb 2006

Thanks VoG.

Good to see you're still around ;o)

Will give it a try.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?