Scribd
Upload
3 views2 pages

Note Guide for Text

The document provides an overview of various Excel text functions, including CONCATENATE, LEFT, RIGHT, and TEXTJOIN, which assist in manipulating and analyzing text data. It highlights practical use cases such as data cleaning, formatting, extraction, transformation, and combining data. These functions are essential for effectively managing textual information in Excel.

Uploaded by

ajileyeife1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views2 pages

Note Guide for Text

The document provides an overview of various Excel text functions, including CONCATENATE, LEFT, RIGHT, and TEXTJOIN, which assist in manipulating and analyzing text data. It highlights practical use cases such as data cleaning, formatting, extraction, transformation, and combining data. These functions are essential for effectively managing textual information in Excel.

Uploaded by

ajileyeife1
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Excel offers a wide range of text functions that help in manipulating and analyzing text data.

Here’s an
overview of some of the most commonly used Excel text functions and their uses:

1. CONCATENATE / CONCAT / TEXTJOIN

 CONCATENATE: Combines multiple text strings into one. (Note: CONCATENATE has been
replaced by CONCAT in newer Excel versions).

=CONCATENATE("Hello", " ", "World")

 CONCAT: Similar to CONCATENATE, but can handle ranges.

=CONCAT(A1:A3)

 TEXTJOIN: Joins text from multiple ranges and/or strings, with a delimiter.

=TEXTJOIN(", ", TRUE, A1:A3)

2. LEFT, RIGHT, MID

 LEFT: Extracts a given number of characters from the start of a text string.

=LEFT(A1, 5)

 RIGHT: Extracts a given number of characters from the end of a text string.

=RIGHT(A1, 3)

 MID: Extracts a substring from a text string starting at any position.

=MID(A1, 3, 4)

3. LEN

 LEN: Returns the number of characters in a text string.

=LEN(A1)

4. FIND and SEARCH

 FIND: Returns the starting position of a substring within a text string (case-sensitive).

=FIND("text", A1)

 SEARCH: Similar to FIND, but case-insensitive.

=SEARCH("text", A1)
5. UPPER, LOWER, PROPER

 UPPER: Converts text to uppercase.


 LOWER: Converts text to lowercase.
 PROPER: Capitalizes the first letter of each word in a text string.

6. TRIM

 TRIM: Removes all spaces from a text string except for single spaces between words.

 CLEAN: Removes all non-printable characters from a text string.

9. TEXT

 TEXT: Converts a value to text in a specific number format.

=TEXT(A1, "dd-mm-yyyy")

10. TEXTSPLIT (Available in Excel 365 and Excel 2021)

 TEXTSPLIT: Splits text into an array by the given delimiters.

excel
Copy code
=TEXTSPLIT(A1, ",")

11. TEXTBEFORE and TEXTAFTER (Available in Excel 365 and Excel 2021)

 TEXTBEFORE: Returns text that appears before a given delimiter.

=TEXTBEFORE(A1, ",")

 TEXTAFTER: Returns text that appears after a given delimiter.

=TEXTAFTER(A1, ",")

Practical Use Cases

 Data Cleaning: Using TRIM, CLEAN, and SUBSTITUTE to remove unwanted spaces and
characters.
 Formatting: Using TEXT to format numbers, dates, and times as text.
 Data Extraction: Using LEFT, RIGHT, MID, FIND, and SEARCH to extract specific parts of text.
 Text Transformation: Using UPPER, LOWER, PROPER to change the case of text.
 Combining Data: Using CONCAT, TEXTJOIN, and CONCATENATE to combine text from
different cells.

These functions are powerful tools for managing and manipulating text data in Excel, enabling users to
clean, transform, and analyze textual information effectively.

You might also like