Excel "compare text string" help

  [DELETED] 13:24 11 Feb 2006

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:


  [DELETED] 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

  [DELETED] 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

Microsoft Surface Book 2 15in review

Illustrator Amy Grimes on how setting up her own eco-brand led to success with clients too

MacBook Pro keyboard issues and other problems

Test : l’enceinte connectée HomePod d’Apple