Text Formulas

-

 

  1. CONCATENATE:

    • Syntax: =CONCATENATE(text1, text2, ...)
    • Example: =CONCATENATE(A1, " ", B1)
    • Combines multiple text strings into one.


  3. LEN (Length):

    • Syntax: =LEN(text)
    • Example: =LEN(A1)
    • Returns the number of characters in a text string.

  4. LEFT:

    • Syntax: =LEFT(text, num_chars)
    • Example: =LEFT(A1, 3)
    • Returns a specified number of characters from the beginning of a text string.

  5. RIGHT:

    • Syntax: =RIGHT(text, num_chars)
    • Example: =RIGHT(A1, 3)
    • Returns a specified number of characters from the end of a text string.

  6. MID:

    • Syntax: =MID(text, start_num, num_chars)
    • Example: =MID(A1, 2, 3)
    • Returns a specific number of characters from a text string, starting at the specified
    • position.

  7. LOWER:

    • Syntax: =LOWER(text)
    • Example: =LOWER(A1)
    • Converts text to lowercase.

  8. UPPER:

    • Syntax: =UPPER(text)
    • Example: =UPPER(A1)
    • Converts text to uppercase.

  9. PROPER:

    • Syntax: =PROPER(text)
    • Example: =PROPER(A1)
    • Capitalizes the first letter of each word in a text string.

  10. TRIM:

    • Syntax: =TRIM(text)
    • Example: =TRIM(A1)
    • Removes leading and trailing spaces from a text string.

  11. SUBSTITUTE:

    • Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
    • Example: =SUBSTITUTE(A1, "apple", "orange")
    • Replaces occurrences of a specified substring with another substring.
    • Removes non-printable characters from text.

  2. EXACT:

    • Syntax: =EXACT(text1, text2)
    • Example: =EXACT(A1, B1)
    • Checks if two text strings are exactly the same.

  3. VALUE:

    • Syntax: =VALUE(text)
    • Example: =VALUE(A1)
    • Converts a text string that represents a number to a numeric value.

  4. LEFTB and RIGHTB:

    • Syntax: =LEFTB(text, num_bytes) and =RIGHTB(text, num_bytes)
    • Example: =LEFTB(A1, 2)
    • Returns a specified number of bytes from the beginning or end of a text string (useful for double-byte character sets).

  5. MIDB:

    • Syntax: =MIDB(text, start_num, num_bytes)
    • Example: =MIDB(A1, 2, 3)
    • Returns a specific number of bytes from a text string, starting at the specified position (useful for double-byte character sets).

  1. FIND:

    • Syntax: =FIND(find_text, within_text, [start_num])
    • Example: =FIND("e", A1)
    • Returns the starting position of a text string within another text string.

  2. SEARCH:

    • Syntax: =SEARCH(find_text, within_text, [start_num])
    • Example: =SEARCH("e", A1)
    • Similar to FIND but is not case-sensitive.

  3. REPLACE:

    • Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
    • Example: =REPLACE(A1, 2, 3, "XYZ")
    • Replaces part of a text string with another text string.

  4. TEXT:

    • Syntax: =TEXT(value, format_text)
    • Example: =TEXT(NOW(), "dd-mmm-yyyy")
    • Converts a value to text with a specified format.

  5. CONCAT:

    • Syntax: =CONCAT(range)
    • Example: =CONCAT(A1:A10)
    • Concatenates the text in a range.

  6. LEFTB, RIGHTB, MIDB:

    • Similar to LEFT, RIGHT, MID, but designed for double-byte character set languages.

  7. EXACT:

    • Syntax: =EXACT(text1, text2)
    • Example: =EXACT(A1, B1)
    • Tests whether two text strings are identical.

  8. CLEAN:

    • Syntax: =CLEAN(text)
    • Example: =CLEAN(A1)
    • Removes non-printable characters from text.

  9. CONCATENATEX:

    • Syntax: =CONCATENATEX(range, delimiter)
    • Example: =CONCATENATEX(A1:A10, ", ")
    • Concatenates values with a specified delimiter.

  10. LEFTB, RIGHTB, MIDB:

    • Similar to LEFT, RIGHT, MID, but designed for double-byte character set languages.

  1. CLEAN:

    • Syntax: =CLEAN(text)
    • Example: =CLEAN(A1)
    • Removes non-printable characters from text.

  2. VALUE:

    • Syntax: =VALUE(text)
    • Example: =VALUE(A1)
    • Converts a text string that represents a number to a number.

  3. CODE:

    • Syntax: =CODE(text)
    • Example: =CODE("A")
    • Returns the ASCII value of the first character in a text string.

  4. CHAR:

    • Syntax: =CHAR(number)
    • Example: =CHAR(65)
    • Returns the character specified by a number (ASCII value).

  5. UNICHAR:

    • Syntax: =UNICHAR(number)
    • Example: =UNICHAR(128516)
    • Returns the Unicode character based on the specified number.

  6. UNICODE:

    • Syntax: =UNICODE(text)
    • Example: =UNICODE("A")
    • Returns the Unicode value of the first character in a text string.

  7. LEFT, RIGHT, MID with FIND/SEARCH:

    • Combining these functions with FIND or SEARCH allows more dynamic text extraction based on specific criteria.

  8. TEXTJOIN:

    • Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
    • Example: =TEXTJOIN(", ", TRUE, A1:A10)
    • Concatenates a list or range of text strings using a delimiter.

  9. SPLIT:

    • Requires using Text to Columns or a combination of formulas to split text based on a specified delimiter.

  10. SUBTOTAL:

    • Syntax: =SUBTOTAL(function_num, range1, range2, ...)
    • Example: =SUBTOTAL(101, A1:A10)
    • Performs a specified calculation (such as sum or average) on a filtered range.

  1. TEXTVALUE:

    • Requires using a combination of functions to convert a text representation of a date or number to a true date or number value.

  2. TEXTSPLIT:

    • Requires using formulas like MID, SEARCH, and LEN to split text based on a specific criterion.

  3. SUBSTITUTE with TEXTJOIN:

    • Syntax: =TEXTJOIN(delimiter, TRUE, SUBSTITUTE(range, old_text, new_text))
    • Example: =TEXTJOIN(", ", TRUE, SUBSTITUTE(A1:A10, " ", "_"))
    • Replaces spaces with underscores in each cell of a range and then joins them with a delimiter.

  4. LEFT, RIGHT, MID with LEN:

    • Combining these functions with LEN allows dynamic extraction of text based on the length of the text.

  5. CONCATENATE with LINE BREAKS:

    • Syntax: =CONCATENATE(A1, CHAR(10), B1)
    • Example: Concatenates text in cell A1 and B1 with a line break.

  6. HYPERLINK:

    • Syntax: =HYPERLINK(link_location, friendly_name)
    • Example: =HYPERLINK("https://www.example.com", "Visit Website")
    • Creates a clickable hyperlink.

  7. CLEAN with SUBSTITUTE:

    • Syntax: =CLEAN(SUBSTITUTE(A1, CHAR(10), ""))
    • Example: Removes line breaks from text using CLEAN and SUBSTITUTE.

  8. TEXT with DATE Function:

    • Syntax: =TEXT(DATE(year, month, day), "mm/dd/yyyy")
    • Example: Converts a date to a specific text format.

  9. CONCAT with IF and ISNUMBER:

    • Syntax: =CONCAT(IF(ISNUMBER(range), range, ""))
    • Example: Concatenates only numeric values from a range.

  10. CONCAT with IFERROR:

    • Syntax: =CONCAT(IFERROR(range, ""))
    • Example: Concatenates values, ignoring errors.

  11. TEXT with NOW Function:

    • Syntax: =TEXT(NOW(), "mm/dd/yyyy hh:mm AM/PM")
    • Example: Formats the current date and time as text.

  12. SEARCH and SUBSTITUTE Combo:

    • Syntax: =SEARCH("search_text", A1)
    • Syntax: =SUBSTITUTE(A1, "old_text", "new_text")
    • Example: Use SEARCH to find the position of a specific text and SUBSTITUTE to replace it.

  13. VALUE with IFERROR:

    • Syntax: =VALUE(IFERROR(A1, ""))
    • Example: Converts text to a numeric value, handling errors gracefully.

  14. LEN with SUBSTITUTE:

    • Syntax: =LEN(SUBSTITUTE(A1, " ", ""))
    • Example: Counts the number of characters excluding spaces.

  15. JOIN with ARRAYFORMULA (for Google Sheets):

    • Syntax: =JOIN(", ", ARRAYFORMULA(A1:A10))
    • Example: Concatenates a range of cells with a delimiter in Google Sheets.

  16. TEXT with CONCATENATE:

    • Syntax: =TEXT(CONCATENATE(A1, B1), "yyyy-mm-dd")
    • Example: Converts concatenated text to a specific date format.

  17. PROPER with TRIM:

    • Syntax: =PROPER(TRIM(A1))
    • Example: Capitalizes the first letter of each word and removes leading/trailing spaces.

  18. EXACT with LOWER or UPPER:

    • Syntax: =EXACT(LOWER(A1), LOWER(B1))
    • Example: Compares two text strings case-insensitively.

  19. TEXT with VLOOKUP:

    • Syntax: =TEXT(VLOOKUP(lookup_value, table_array, col_index_num, FALSE), "mm/dd/yyyy")
    • Example: Retrieves a date value using VLOOKUP and formats it.

  20. FILTER with TEXT:

    • Syntax: =TEXT(FILTER(A1:A10, B1:B10="Criteria"), "mm/dd/yyyy")
    • Example: Filters dates based on a specific criteria and formats them.

  21. TEXT with MID and SEARCH:
    • Syntax: =TEXT(MID(A1, SEARCH("start", A1) + 5, 10), "mm/dd/yyyy")
    • Example: Extracts a substring from a text string using MID and SEARCH, then formats it as a date.
    1. IF with ISNUMBER:
    • Syntax: =IF(ISNUMBER(FIND("text", A1)), "Found", "Not Found")
    • Example: Checks if a specific text is present in a cell.
    1. TEXT with CONCATENATE and IF:
    • Syntax: =TEXT(CONCATENATE(A1, B1), "yyyy-mm-dd")
    • Example: Concatenates two text cells and converts them to a specific date format.
    1. LEFT with FIND/SEARCH and LEN:
    • Syntax: =LEFT(A1, FIND(" ", A1)-1)
    • Example: Extracts the first word from a text string.
    1. REPT:
    • Syntax: =REPT(text, number_of_times)
    • Example: =REPT("ABC", 3) repeats the text "ABC" three times.
    1. SEARCH with ARRAYFORMULA (for Google Sheets):
    • Syntax: =ARRAYFORMULA(SEARCH("text", A1:A10))
    • Example: Searches for a specific text in a range of cells in Google Sheets.
    1. TEXT with LEFT and LEN for Conditional Formatting:
    • Syntax: =LEN(LEFT(A1, 5))=5
    • Example: Uses LEN and LEFT to conditionally format cells with a specific length.
    1. TEXTJOIN with IF:
    • Syntax: =TEXTJOIN(", ", TRUE, IF(condition, A1:A10, ""))
    • Example: Joins text based on a condition.
    1. HYPERLINK with CONCATENATE:
    • Syntax: =HYPERLINK(CONCATENATE("https://www.example.com/", A1), A1)
    • Example: Creates hyperlinks based on text in a cell.
    1. UNION with Power Query (Get & Transform):
    • Combines data from multiple text files using Power Query in Excel.

These text formulas can be very useful for manipulating and analyzing text data in Excel.

Comments

Post a Comment

Popular posts from this blog

Lila's Journey to Becoming a Data Scientist: Her Working Approach on the First Task

-
  Education and Skill Acquisition: Lila, with an economics background, decides to transition to data science. Enrolls in the IBM Data Science Professional Certificate for comprehensive training. Gains proficiency in statistics, machine learning, and programming languages. Building a Strong Foundation: Develops a deep understanding of data manipulation and visualization using Python libraries. Visualization for Storytelling: Learns to create informative visualizations, aiding in effective communication of findings. Hands-On Experience: Engages in Kaggle competitions and personal data projects for practical experience. Establishes a GitHub profile to showcase her projects and skills. Data Wrangling and Preprocessing: Focuses on data cleaning, preprocessing, and handling missing data and outliers. Communication and Storytelling: Hones data storytelling skills using tools like Matplotlib and Plotly. Networking and Collaboration: Actively participates in data science communities, attend...
Read more

Notes on Hiring for Data Science Teams

-
  Unicorn Myth in Hiring: Companies often seek individuals with a myriad of skills for data science roles. Ideal candidates possess domain-specific knowledge, excel in data analysis, and have strong presentation and storytelling skills. However, finding such a "unicorn" is rare, and the focus should shift to other essential qualities. Resonance with Company DNA: The key is to identify applicants who resonate with the company's values and business domain. While analytics skills can be taught, passion for the business is intrinsic and contributes significantly to productivity. Curiosity Matters: Curiosity is a top priority in hiring for a data science team. Candidates should demonstrate a broad curiosity, showing interest in various aspects of their surroundings beyond just data science. Sense of Humor: A lighthearted approach is crucial, as being too serious may hinder collaboration and creativity. A sense of humor aids in navigating challenges and fosters a positive work ...
Read more

switch functions

-
  Nested IF Function : The  nested IF  function allows you to perform tests with multiple conditions and return different values based on the results of those tests. It is called “nested” because one IF function is embedded inside another IF function. Syntax of a generic nested IF formula: =IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4) ) ) Each subsequent IF function is embedded into the  value_if_false  argument of the previous function. SWITCH Function : The  SWITCH  function is a more compact alternative to nested IF statements. It compares an expression against a list of values and returns the result according to the first matching value. Syntax of the SWITCH function: =SWITCH(expression, value1, result1, [default or value2, result2], ... [default or valueN, resultN]) It has four arguments: expression : The value to compare against the list of values. valueN : A value com...
Read more