I’ve been playing around with Excel pretty extensively and have discovered that it’s a powerhouse when it comes to manipulating text. With the numerous text functions available, you can slice and dice strings, switch up text cases, pinpoint specific substrings, and even count how many characters you’re dealing with. This handy set of tools is like a Swiss Army knife for anyone who needs to handle data that’s more about words and less about numbers.

During my exploration, I came across a bunch of these text functions and couldn’t help but dive in. It’s fascinating to see how functions like CONCAT, SUBSTITUTE, and TRIM can transform a chaotic string of text into something meaningful. You can convert strings to a proper case, pull out specific characters, or clear away unwanted spaces. Plus, if you’re looking to get some practice, there are sample files available to download and follow along. It feels like these functions are the unsung heroes that ramp up productivity and precision in data analysis.

1. LEN Function

Syntax

=LEN(text)

Arguments

  • text: The text string whose characters you’re counting.

Example

I used LEN to figure out “Hello, World” has 11 characters, counting the space. Here’s what it looked like:

excel-len-function-example-1

Curious case: “22-Jan-2016” shows up as 11 characters, but LEN tells me 5. That’s because LEN only looks at the actual string length, not the formatting.

excel-len-function-example-2

By the way, if you’re into word counts, check this out: How to COUNT Words in Excel

2. FIND Function

Syntax

FIND(find_text, within_text, [start_num])

Arguments

  • find_text: The substring I’m looking to find.
  • within_text: The string where I’m searching for find_text.
  • [start_num]: The optional index from where I start my search, defaulting to 1 if not specified.

Example

Consider I’ve got to pinpoint where a colon appears in a text. I’ll pop this into my formula:

=FIND(":", A1)

If A1 has ‘Project: Blue Sky’, FIND tosses me back ‘8’. That’s ’cause ‘:’ sits at the 8th position. Now, combining that with MID and LEN, I can snag the project’s name piece of cake.

3. SEARCH Function

Syntax

=SEARCH(find_text, within_text, [start_num])

Arguments

  • find_text: The specific text I’m aiming to locate.
  • within_text: The string or cell where I’m conducting the search.

Example

Let’s say I’m trying to pinpoint where the letter “P” first appears. When I set up my formula like this:

=SEARCH("P", "Apple", 1)

I get back the number 1, indicating “P” is the first character. However, “P” also pops up as the 6th character in “Pineapple.” Since SEARCH only finds the first occurrence or where I ask it to start, I’d specify that differently if I wanted to find the other “P.” Check out how it’s done in this image:

excel-search-function-example-1

For more scenarios, you can grab this example worksheet: Download Example.

4. LEFT Function

Syntax

LEFT(text, num_chars)

Arguments

  • text: The string from which I want to pull characters.
  • [num_chars]: How many characters to extract, counting from the left.

Example

When I need to grab the first few characters from a string, I use LEFT. For instance, to get the first five characters, it’s as simple as:

=LEFT("12345abc",5)

But let’s say I want more than just a static value—I can get dynamic by combining it with FIND:

=LEFT(A1, FIND(" ", A1) - 1)

Now imagine A1 contains “Hello World”. This combo would give me “Hello”.

Here’s how it looks visually with an example:

excel-left-function-example-1

For a more complex case, if I want to extract the name from a full string:

excel-left-function-example-2

By using LEN and FIND with LEFT, I can cleverly pull out just the first name. Cool, right?

5. RIGHT Function

Syntax

RIGHT(text, num_chars)

Arguments

  • text: The string from which to extract the end characters.
  • num_chars: The number of characters to extract from the end of the text. Optional.

Example

I like to keep things simple. Take this scenario: I’ve got the text “Excel is powerful” and I want to grab the last 6 characters. Using the RIGHT function, I’m able to easily extract “owerful” like so:

RIGHT("Excel is powerful", 6)

But what if the number of characters isn’t constant? Suppose I only want the surname from a full name but don’t know how long the surname is. By combining LEN and FIND, I can dynamically calculate the number of characters to extract:

First, I determine the full length of the text. Let’s say the cell A1 has the name “John Smith”. Here’s how I find the last name:

LEN(A1) - FIND(" ", A1)

Then, applying the RIGHT function gets me just “Smith”. Clever, right?

Arguments

  • value1: This could be a single text string or a reference to a cell containing the text from which I am extracting characters.
  • value2: Sometimes I do not use this argument, but when I do, it’s to specify an optional number of characters to extract if necessary.

6. MID Function

Syntax

Here’s how we structure a MID formula:

MID(text, start_num, num_chars)

Arguments

  • text: The string from which I want to extract characters. This can be directly input or can be a cell reference.
  • start_num: The position within text where the substring starts. The first character in text is 1.
  • num_chars: How many characters to pull from start_num. If the number of characters leads beyond the text length, the function returns characters up to the end.

Example

To demonstrate, let’s say I’m working with the following scenarios:

  • Extracting 6 characters from the 6th character of a string.
  • Trying to extract characters starting at a negative position, which results in an error.
  • If I ask for 0 characters, I’ll get an empty string.
  • Requesting extraction with a negative character count leads to an error, as does starting from position zero.
  • Inserting a text string directly into the MID function.

Suppose my text is “OpenAI is amazing”, and I use:

=MID("OpenAI is amazing", 7, 6)

This gives me “is ama” which starts at the 7th character for a length of 6 characters.

Here’s how things look in an Excel cell example:

Excel MID function example

7. LOWER Function

Syntax

Here’s how simple it is to format text in Excel:

LOWER(text)

Arguments

  • text: This is what I feed into the formula to get everything in lowercase.

Example

Check out this neat trick: by using LOWER in Excel, I can flip any uppercase letters to lowercase like a pro – numbers stay just as they are, though.

excel-lower-function-example-1

Cool, right? Just like that, whatever text I type comes out chilled in all small letters.

8. PROPER Function

Syntax

PROPER(text)

Arguments

  • text: The string I want to be formatted to the proper case.

Example

I used the PROPER function in a spreadsheet to change “john doe” to “John Doe.” Check out how it turned the first characters of each word to uppercase:

excel-proper-function-example-1

Here’s another illustration, where I ensured first names and last names had only the initial letter capitalized for a list of people:

excel-proper-function-example-2

9. UPPER Function

Syntax

UPPER(text)

Arguments

  • text: The string I want to turn into all uppercase letters.

Example

Here’s how I use UPPER to capitalize all the letters in a name:

excel-upper-function-example-1

10. REPT Function

Syntax

Here’s how you lay it out:

=REPT(text, number_times)
  • text: It’s the characters you want to repeat.
  • number_times: How many times you want your text to appear.

Example

I often use the REPT function for visual impact in spreadsheets. Let’s say you want to make simple bar charts within a cell. Check out how I mix symbols and text:

Data REPT Formula Result
A =REPT("A", 3)
% =REPT("%", 5)
123 =REPT("123", 2)

These commands space out texts or symbols, repeating them as many times as I need. See an example of a chart created with REPT:

excel-rept-function-example-1

Similar Posts