Share this article:

Following up on our post about some of the most commonly used Excel text functions, today we’re going to focus on cleaning up a block of text that includes paragraph breaks and characters

clean up text in excel

Here we’re looking at the descriptions of five videos on a made up YouTube channel. Video 1’s description has the proper formatting. All of our descriptions should meet the following criteria:

  • Short description
  • Links to both our instagram handle and snapchat handle
  • Free of any extra spaces, paragraph breaks, and special characters.

Our ultimate goal is to clean all of these cells up using ONLY formulas rather than CTRL+F or doing this process manually.

Note: In this case cleaning up these cells by hand would probably be faster than learning/using the formulas below. However, if you’re dealing with hundreds or even thousands of rows of data, I’m hoping a few of these tricks will open up the possibility for you of using these tips on a much larger scale.

 

Video 2:

trim function excel

The first thing I noticed in this cell is a space at the beginning of the box. Let’s use TRIM to remove trailing spaces at the beginning and end of a cell.

Trim function in Excel

Although it remove the space at the beginning, we’re still left with the paragraph breaks at the bottom and the meaningless text ‘video431’. Let’s start by replacing the text.

The SUBSTITUTE Function

The Substitute function takes three mandatory arguments. The data you’re working with (B3 in our case), what text you’re looking for, and what text you want to replace.

substitute function

In this case we’re targeting the test “video431/” and replacing it with nothing. In C3 is our cleaned up cell.

Introducing the CHAR Function

The CHAR function is an amazing function to get familiar with if you’re cleaning up text data at all. Every OS has a character set (hence “CHAR”) used to classify characters in Excel. Here is the link to the Windows List. Capital ‘A’ is character 65. Lower case ‘g’ is 103. And the ‘newline’ character aka ‘carraige return’ is character number 10. Because a ‘carraige return’ has a specific char number, we can use this to our advantage to find and replace these line breaks systematically.

Counting the Number of Paragraph Breaks in the Cell

One of my frequently used tricks when dealing with Text data is to count the number of times something appears in a cell. It allows me to target characters much more easily when I have to remove them.

Find the number of characters in a text string

Formula:

=LEN(B3)-LEN(SUBSTITUTE(B3,CHAR(10),""))

There’s a lot going on in this formula so let’s break it down

LEN(B3): This finds the total length, or number of characters in B3. In our case, that length is 144.

LEN(SUBSTITUTE(B3,CHAR(10),””)): This is the length of the same string, but with the paragraphs removed. In our case that total is 138.

144-138 = 6, so we know there are a total of 6 paragraph breaks in our cell. Since we know there are two between “with this…” and “Follow us”, that must mean there are four more below the ‘@randominstagramhandle’

character 10

It’s kind of a crude drawing, but the tick marks are next to each of the four paragraph breaks we still need to remove.

Introducing the CLEAN function

According the official documentation, the clean function “Removes all nonprintable characters from text”. This includes good ‘ol character 10.

Clean Function

Although we got rid of the four paragraph breaks at the end, we also removed the ones in the middle of our description! That won’t work.

Introducing the REPLACE Function

The Replace function is a cousin of the Substitute Function

replace function

It takes four arguments:

  1. Data to clean (B3)
  2. Where to start in the data. Here I entered len(b3) – 3 because we want to start at character number 141 out of 144
  3. How many characters to replace (4)
  4. What to replace it with (nothing)

Beautiful. That gets us close to the finish line. From there let’s concatenate our snapchat handle on to the block of text.

concatenate excel

By adding on one more line break and our snapchat handle, our description is finished!

 

Video 3:

This one should be a little easier.

clean up text in Excel

It looks like we have some extra characters at the top of the cell and we’re missing the full call to action for our snapchat handle.

substitute function excel

By inserting the three asterisks inside a Substitute function, we can get rid of them immediately.

After that, we notice we still have an additional paragraph break at the top of the cell

paragraph break excel

To remove this we’ll use the optional argument of the substitute function.

substitute function instance number

Notice at the end of our substitute formula is the number ‘1.’ This fills the optional argument of “instance number”. If you’re replacing char(10), which char(10) do you want to replace? If you enter no arguments here, it will replace all of them. If you provide an argument, it will replace that instance of the character. In our case, it’s the first instance.

Note: Even though it looks like there is still a paragraph break, there is not (row height still affected by cell b4)

Lastly, we have to clean up our snapchat handle.

substitute function in excel

Since we have the correct layout of our social media CTA, we can substitute out the abbreviated one for the correct one using SUBSTITUTE.

All Done!

 

Video 4:

Clean text in Excel

With this description it looks like we have a random parentheses showing up along with incomplete instagram/snapchat callouts.

substitute function excel

The first thing we’ll do here is grab the open parentheses and the space all in one formula. Notice that you can include the parentheses and space together and replace both with an empty string.

The final thing we’ll do here is create a “nested” substitute function to clean up the social media call-to-actions.

nested substitute function

After cleaning up the instagram handle, we can wrap that entire function in the substitute function to replace the snapchat handle at the same time.

(Note: In that formula, B9 and B10 are the correct mentions of the social media CTA)

 

Video 5:

rept function excel

In this scenario we have a good description and no social media call to actions. All we have to do hear is concatenate them to our description and we’re done.

char10 excel

By using the ampersand, we can concatenate the social media CTAs and provide proper spacing using good ‘ol char(10), the line break. But there is a slightly easier way to write this. Notice I had to add two char(10)s in a row to get enough space in between the next block.

The REPT Function

The repeat function is a great one to have in your arsenal as well. This takes two arguments:

  1. What you want to repeat
  2. How many times.

rept function

Using the Rept function here has the same effect. For those of you that would prefer to use the Concatenate function here instead of the ampersand, this is how it would look:

concatenate function

Using concatenate vs. ampersand is mainly a preference thing. Some may like the cleaner look of comma separated values vs. the ampersand.

In either case, we’re done! All of our descriptions are cleaned up and we’re ready to publish these to our YouTube channel.

clean up text in excel

Functions Mentioned in this blog post: