question

navk-3982 avatar image
0 Votes"
navk-3982 asked ·

Using the Text function to derive a 4 letter word from 2 word

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.

office-excel-itpro
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @navk-3982
Please check if Lz-3068's reply is helpful to you.
Any updates, please let us know.

0 Votes 0 ·

1 Answer

Lz-3068 avatar image
1 Vote"
Lz-3068 answered ·

Hi @navk-3982

73231-demo.png

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)
 )


demo.png (39.9 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@navk-3982
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.

0 Votes 0 ·