Re: Duplicates in excel that aren't 100% DUPLICATES ...
- From: Joel <Joel@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 2 May 2008 10:43:01 -0700
Of course, Of course.
Street_Num =LEFT(G17,FIND(" ",G17)-1)
First_Word =MID(G17,FIND(" ",G17)+1,LEN(G17))
Second_Word =MID(First_Word,FIND(" ",First_Word)+1,LEN(First_Word))
'remove the 2nd word from the 1st word
First_Word = LEFT(First_Word ,FIND(" ",First_Word )-1)
"jeisdorfer@xxxxxxxxx" wrote:
So that ALMOST works, except, for the street name column -- I only.
want the name of the street because the end of the string varies (i.e.
AVE, Avenue, Apt #s, etc.). Is there a way to select the words IN
BETWEEN the first and second space? Hmm...
Then I'll add the Street #, Street Name, and ZIP columns together and
find duplicates from there ...
On May 2, 3:10 am, Joel <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
The best way is to use two new auxilary columns. The two functions will look
for the first space and put the data to the left of the space (the street
number) in one cell and the string to the left of the space in a second
column.
Auxilary Address column
=LEFT(G17,FIND(" ",G17)-1)
the FIND(" ",G17) gets the position of the frist space. Subtracting 1 from
the space gets the length of the street number in characters. Then the LEFT
extracts the street number. G17 is the cell where the street name and number
are located.
street name column
=MID(G17,FIND(" ",G17)+1,LEN(G17))
Mid works the same as left put extracts the middle of a string. We use the
same FIND command but add one to get the start of the street name. Mid also
requies a number of characters which has to be as long as the street name but
can be longer. I just use the entire string length to make the code simple.
"jeisdor...@xxxxxxxxx" wrote:
I'm having a merging nightmare inexcel...
I'm trying to get rid ofduplicatesof names and address from a list
of about 6,000 however, the names and addresses are not uniformly
formatted (i.e. some have Apt. # in column Address 1 whereas some are
in column Address 2, some have AVE vs. Avenue, some names are Mr. and
Mrs. LastName others are FirstName and FirstName LastName. etc. etc.
the mess goes on).
Is there a formula I could put in a column next to the address line 1
just the street number followed by the first word? i.e. "111
Westbourne" from "111 Westbourne Terr. #3" I think that would help me
sort through the mess.
Is this the easiest way to go? Baaah....
- References:
- Duplicates in excel that aren't 100% DUPLICATES ...
- From: jeisdorfer
- RE: Duplicates in excel that aren't 100% DUPLICATES ...
- From: Joel
- Re: Duplicates in excel that aren't 100% DUPLICATES ...
- From: jeisdorfer
- Duplicates in excel that aren't 100% DUPLICATES ...
- Prev by Date: Ignoring #N/A in a DSUM function
- Next by Date: Deleted Document
- Previous by thread: Re: Duplicates in excel that aren't 100% DUPLICATES ...
- Next by thread: Do NOt Know Which Function to Use
- Index(es):
Relevant Pages
|
