Excel Learning on 30th June 2024

in #excel6 months ago
  1. Capitalise the alphabet
    kevin cookie company
    =proper (col_name)
    Kevin Cookie Company

  2. Trim
    Kevin Cookie Company
    =trim(col_name)
    Kevin Cookie Company

  3. Concat
    =concat(A10, " ", B10)
    Kevin Cookie Vs KevinCookie

  4. Text Join
    =textjoin(" ", TRUE, A18:C18), format: delimiter,ignore empty col? Yes, Col range)

  5. Extract Company Address
    123 Main Street, 05486
    =right(A22, 5)
    result:05486

  6. Extract word before "@" ,精准extract“@” 之前的字
    kevin@kevincookiecompany.com
    left(A28, find("@", A28)-1)
    result: kevin
    sue@kevincookiecompany.com
    result: sue

7.Nested if
=IF(M30=P30,N30,IF(M31=P30,N31,"unknown"))

image.png
OR xlookup function (more easier and straight forward)