Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching when spaces are involved
Afternoon
Im currently matching up two tables of data using the Index function. =INDEX(UPRNmatch.xls!Gaz,MATCH(N3669,UPRNmatch.xls !Match,0),1) (Where Gaz & Match are range names). This is working mostly fine, however, it is failing to match some data. Upon investigating it seems that this is when there is a space at the end of the data field in one case, but not the other. Does anyone know how to make the search ignore any space at the end of a data field? The data it is comparing is addresses, so the unwanted space is at the end of the address. Cheers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching when spaces are involved
One way
use the TRIM() function to eliminate the spaces at the end (it'll also change double spaces within the text to single spaces) Another way This is an arrya formula, so enter it with Shift+Ctrl+Enter =INDEX(UPRNmatch.xls!Gaz,MATCH(N3669,TRIM(UPRNmatc h.xls!Match),0),1) "JaB" wrote: Afternoon Im currently matching up two tables of data using the Index function. =INDEX(UPRNmatch.xls!Gaz,MATCH(N3669,UPRNmatch.xls !Match,0),1) (Where Gaz & Match are range names). This is working mostly fine, however, it is failing to match some data. Upon investigating it seems that this is when there is a space at the end of the data field in one case, but not the other. Does anyone know how to make the search ignore any space at the end of a data field? The data it is comparing is addresses, so the unwanted space is at the end of the address. Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count matching cells | Excel Worksheet Functions | |||
Removing spaces in cells with data in it | Excel Discussion (Misc queries) | |||
how do I remove empty spaces trailing a text string? | Excel Worksheet Functions | |||
Stripping out imbedded spaces in a cell/row | Excel Worksheet Functions | |||
remove spaces in text in excel | Excel Discussion (Misc queries) |