Need to find the 1st two characters of the 1st two words

[Tom]

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

[N Harkawat]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

[Tom]

Thanks, that works great! I appreciate it!!!

--
Thanks,
Tom


"N Harkawat" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>
>