Text Processing Functions (LEFT, RIGHT, CONCATENATE, etc.)

1. When text data needs to be processed: Splitting or merging names, addresses, or other string data.

2. When text data lacks consistency: Removing unnecessary spaces or handling incorrect capitalization.

Solutions:

1. LEFT, RIGHT, MID Functions: Extracting Parts of Text

• Problem: Extracting specific parts from a customer ID.

• Usage:

  =LEFT(A1, 5)  ' Extracts the first 5 characters
  =RIGHT(A1, 3) ' Extracts the last 3 characters
  =MID(A1, 3, 4) ' Extracts 4 characters starting from the 3rd character
  
  

2. CONCATENATE and TEXTJOIN Functions: Combining Text

• Problem: Merging first and last names into a single column.

• Usage:

  =CONCATENATE(B1, " ", C1)
  =TEXTJOIN(" ", TRUE, B1, C1)
  
  

• Tip: TEXTJOIN allows for more flexibility by including multiple conditions.

3. PROPER, UPPER, LOWER Functions: Changing Text Case

• Problem: Ensuring text follows a consistent capitalization format.

• Usage:

  =PROPER(A1) ' Capitalizes the first letter of each word
  =UPPER(A1)  ' Converts all text to uppercase
  =LOWER(A1)  ' Converts all text to lowercase
  
  

4. LEN and FIND Functions: Finding Text Length and Position

• Problem: Determining the length of a string or locating a specific character.

• Usage:

  =LEN(A1)   ' Returns the length of the string
  =FIND("@", A1) ' Finds the position of "@" in the text
  
  

Table: Customer Information

1. LEFT, RIGHT, MID Functions: Extracting Parts of Text

Example:

• Problem: Extracting specific parts from the customer ID.

LEFT Function:

• Usage:

  =LEFT(A2, 5)
  
  

• Result: 12345 (First 5 characters of A2)

RIGHT Function:

• Usage:

  =RIGHT(A2, 3)
  
  

• Result: 789 (Last 3 characters of A2)

MID Function:

• Usage:

  =MID(A2, 6, 2)
  
  

• Result: A7 (2 characters starting from the 6th position of A2)

2. CONCATENATE and TEXTJOIN Functions: Combining Text

Example:

• Problem: Merging first and last names into a full name.

CONCATENATE Function:

• Usage:

  =CONCATENATE(B2, " ", C2)
  
  

• Result: James Smith

TEXTJOIN Function:

• Usage:

  =TEXTJOIN(" ", TRUE, B2, C2)
  
  

• Result: James Smith

Application:

• Combining multiple fields:

  =TEXTJOIN(" - ", TRUE, B2, C2, A2)
  
  

  • Result: James Smith - 12345A789

3. PROPER, UPPER, LOWER Functions: Changing Text Case

Example:

• Problem: Convert email addresses to uppercase or lowercase.

UPPER Function:

• Usage:

  =UPPER(D2)
  
  

• Result: JAMES.SMITH@MAIL.COM

LOWER Function:

• Usage:

  =LOWER(D2)
  
  

• Result: james.smith@mail.com

PROPER Function:

• Usage:

  =PROPER(D2)
  
  

• Result: James.Smith@Mail.Com

4. LEN and FIND Functions: Finding Text Length and Position

Example:

• Problem: Finding the total length of an email address and locating a specific character.

LEN Function:

• Usage:

  =LEN(D2)
  
  

• Result: 21 (Character count of D2)

FIND Function:

• Usage:

  =FIND("@", D2)
  
  

• Result: 12 (Position of "@" in D2)

Comprehensive Use Cases

1. Display Full Customer Information in One Cell

• Problem: Display customer data in the format: [Customer ID] First Name Last Name - Email.

• Usage:

  =TEXTJOIN(" - ", TRUE, A2, B2, C2, D2)
  
  

• Result: 12345A789 - James Smith - james.smith@mail.com

2. Convert Domain Name to Uppercase

• Problem: Convert the domain name in an email address to uppercase.

• Usage:

  =UPPER(MID(D2, FIND("@", D2)+1, LEN(D2)-FIND("@", D2)))
  
  

• Result: MAIL.COM

Additional Functions

• TRIM:
  • Removes unnecessary spaces.
  • Example: =TRIM(A1).

• TEXT:
  • Formats numbers or dates.
  • Example: =TEXT(A1, "yyyy-mm-dd").

• REPLACE, SUBSTITUTE:
  • Replaces parts of text.
  • Example: =SUBSTITUTE(A1, "error", "fixed").