Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need to find the 1st two characters of the 1st two words
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Thanks, that works great! I appreciate it!!!
-- Thanks, Tom "N Harkawat" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit + Find | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
Using the Find tool in EXCEL | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
VB Find and Replace | Excel Worksheet Functions |