This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(272, 1%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENK%3C/text%3E%3C/svg%3E)
So I have been given a task to using the text function to derive 4 letter words from sports names. For example, Tennis should be turned into TENN, and so on, however, I'm having difficulty with the two letter sports such as "American football". I have been asked to create a 4 letter word for it using a function as well however it should be the first three letters of the first word combined with the first letter of the second word, so American football would be "AMEF". I have figured out how to use the left function to get the codes for one word sports such as tennis, hockey and such but I don't know how to get the code for two letter words.
Hi @nav k
Please check if Lz-3068's reply is helpful to you.
Any updates, please let us know.
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Hi @nav k
in B2:
=UPPER(
IF(ISNUMBER(SEARCH(" ",A2)),
LEFT(A2,3) & MID(A2,SEARCH(" ",A2)+1,1),
LEFT(A2,4)
)
)
in C2 (if you run Excel 365 with the LET function):
=LET(
SpacePos, SEARCH(" ",A2),
Code, IF(ISNUMBER(SpacePos),
LEFT(A2,3) & MID(A2,SpacePos+1,1),
LEFT(A2,4)
),
UPPER(Code)
)
@nav k
Have you checked if Lz-3068's reply is helpful to you?
If it is helpful, please accept it as answer to help people who have similar issue.