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

Question

question

navk-3982 asked · Mar 01 at 09:57 PM

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

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

erinding-msft · Mar 02 at 09:42 AM

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

0 ·

Answer 1 · 2021-03-02T01:35:02.047Z

Lz-3068 answered · Mar 02 at 01:35 AM

Hi @navk-3982

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

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

erinding-msft · Mar 10 at 06:00 AM

@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 ·

question