Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell search, match part of string
I've got two spreadsheets that have a column with the same data, more or less.
i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester " so i've got this bit of macro where sitename comes from spreadsheet 1 and is a string and osheet.Range("A" & counter) comes from spreadsheet 2. For counter = 1 To 50 Step 1 If sitename = osheet.Range("A" & counter) Then do stuff Next counter The problem is that with the example above, i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true Is there any way of getting around this. Maybe removing the empty spaces at the end of a cell? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell search, match part of string
trim(sitename) does the trick
google is my friend I guess "A1pro" wrote: I've got two spreadsheets that have a column with the same data, more or less. i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester " so i've got this bit of macro where sitename comes from spreadsheet 1 and is a string and osheet.Range("A" & counter) comes from spreadsheet 2. For counter = 1 To 50 Step 1 If sitename = osheet.Range("A" & counter) Then do stuff Next counter The problem is that with the example above, i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true Is there any way of getting around this. Maybe removing the empty spaces at the end of a cell? TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell search, match part of string
Hi,
Maybe the following would give you some ideas 1. To ignore leading and trailing spaces: If trim(sitename) = trim(osheet.Range("A" & counter)) 2. To apply #1 AND ignore the case If ucase(trim(sitename)) = ucase(trim(osheet.Range("A" & counter))) 3. If you want to ignore only the spaces at the end of the string, use Rtrim and if you want to ignore only the spaces at the beginning, use Ltrim. -- Hope that helps. Vergel Adriano "A1pro" wrote: I've got two spreadsheets that have a column with the same data, more or less. i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester " so i've got this bit of macro where sitename comes from spreadsheet 1 and is a string and osheet.Range("A" & counter) comes from spreadsheet 2. For counter = 1 To 50 Step 1 If sitename = osheet.Range("A" & counter) Then do stuff Next counter The problem is that with the example above, i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true Is there any way of getting around this. Maybe removing the empty spaces at the end of a cell? TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell search, match part of string
Try this
If sitename = osheet.clean(Range("A" & counter)) Then "A1pro" wrote: I've got two spreadsheets that have a column with the same data, more or less. i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester " so i've got this bit of macro where sitename comes from spreadsheet 1 and is a string and osheet.Range("A" & counter) comes from spreadsheet 2. For counter = 1 To 50 Step 1 If sitename = osheet.Range("A" & counter) Then do stuff Next counter The problem is that with the example above, i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true Is there any way of getting around this. Maybe removing the empty spaces at the end of a cell? TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell search, match part of string
Joel,
Just a heads up - Clean only does non-printing characters (and i suspect only those below 128). a = " a " ? len(a) 3 ? len(Application.Clean(a)) 3 ? len(trim(a)) 1 (it doesn't clean chr(160), "nbsp" often copied in from the internet. as an example) -- Regards, Tom Ogilvy "Joel" wrote: Try this If sitename = osheet.clean(Range("A" & counter)) Then "A1pro" wrote: I've got two spreadsheets that have a column with the same data, more or less. i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester " so i've got this bit of macro where sitename comes from spreadsheet 1 and is a string and osheet.Range("A" & counter) comes from spreadsheet 2. For counter = 1 To 50 Step 1 If sitename = osheet.Range("A" & counter) Then do stuff Next counter The problem is that with the example above, i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true Is there any way of getting around this. Maybe removing the empty spaces at the end of a cell? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
search value as part of a cell value | Excel Programming | |||
Move Part of Cell String | Excel Programming | |||
select part of a cell value string | Excel Discussion (Misc queries) | |||
Search on a part of the cell value | Excel Programming |