To capture the first 2 characters of the first 2 words use this fomula
assuming that the words are in column A
=UPPER(LEFT(A1,2)&MID(SUBSTITUTE(A1," ","{",1),SEARCH("{",SUBSTITUTE(A1,"
","{",1))+1,2))
To address the second part of your problem we will need to identify all
duplicates and revise the formula slightly by changing the 1 to 2 as
follows:
=UPPER(LEFT(A4,2)&MID(SUBSTITUTE(A4," ","{",2),SEARCH("{",SUBSTITUTE(A4,"
","{",2))+1,2))
To identify duplicates on a new column using countif function identify all
entries that are more than 1 and then sort
"Tom" wrote in message
...
Does anyone know how to come up w/ a smart function that will exctract the
first 2 letters of the first 2 words from a string.
For instance, I may have the following data in a column:
Alpha Bravo Zulu Yankee
Delta Kilo
Mike Tango Papa
I'd like to have the 4 characters strung together, such as:
- ALBR
- DEKI
- MITA
Now, the following would be a BIG PLUS!!! Let's say I had a 4th record
such as "Mke Tango Yankee". In this case, I would have MITA twice since
the 1st two words are the same (3rd and 4th record). Is there now a
chance to take the 3rd word for the example "duplicate value"? So, I'd
come up with:
- ALBR
- DEKI
- MITA
- MIYA
Thanks,
Tom
|