How to Truncate Text in Excel


Excel featured

Truncating a textual content string in Excel permits you to lower off pointless components to it. If you’re not sure how, observe this information.

The fantastic thing about Microsoft Excel is in the performance it gives. If you need to do one thing in Excel, you solely want to discover the precise perform to do it.

If you’re making an attempt to clear up some textual content, for instance, you may want to take into account truncating it. Thankfully, Excel gives various capabilities to assist you to lower down a textual content string in Excel.

If you’re not sure how to truncate textual content in Excel, observe our steps beneath.

How to Truncate Text in Excel Using RIGHT, LEFT, or MID Functions

The finest method to truncate textual content in excel is to use the RIGHT, LEFT, or MID capabilities. These capabilities all work in very related methods to assist you to take away various characters from a textual content string in Excel.

Explaining RIGHT, LEFT, and MID Functions in Excel

As the names counsel, these capabilities enable you to take away various characters from a textual content string, ranging from the left or proper. You can even begin it from a customized place utilizing MID.

The syntax for these capabilities is as follows:

=RIGHT(textual content, num_chars)

=LEFT(textual content, num_chars)

=MID(textual content, start_num, num_chars)

LEFT and RIGHT work in the identical method. Replace textual content with the textual content string (or cell reference containing a textual content string) and substitute num_chars with the variety of characters to hold from the start line on the left or proper. For MID, substitute textual content and num_chars in the identical method.

You’ll additionally want to substitute start_num with the character place you need to begin in for MID. For instance, if a textual content string is 8 characters lengthy, and you choose a start_num worth of 3, the system will ignore characters 1 and a pair of and present the remaining characters (primarily based on the num_chars worth) shifting left.

Example MID RIGHT and LEFT formulas in Excel

The LEFT, MID, and RIGHT formulation in Excel.

As we’ve talked about, you may substitute the textual content argument with both a textual content string or a cell reference containing a string. If you need to use a textual content string the system instantly, nonetheless, make certain to wrap the textual content string in quote marks to guarantee it’s parsed appropriately.

How to Use LEFT, RIGHT, and MID in Excel

To assist clarify how to use LEFT, RIGHT, and MID capabilities to truncate textual content in Excel, we’ll use a situation.

A cell (A2) comprises a textual content string—It is a pleasant day at this time in May. This textual content string comprises eight phrases and 29 characters. If you need to take away any of those characters, you should use the RIGHT, LEFT, or MID capabilities to do it. Let’s assume you need to present ten characters from the textual content string talked about above.

For MID, we’ll assume you need to begin after the letter a (starting with character quantity 8).

An example of the MID RIGHT and LEFT Functions in Excel

Using every of those capabilities would return completely different outcomes:

  • LEFT: It is a ni
  • MID:  good day (beginning with an area)
  • RIGHT: day in May

You can apply the foundations from this situation to your individual LEFT, RIGHT, or MID formulation.

How to Truncate Number Values in Excel

The steps above solely work for truncating textual content strings. If you need to truncate a quantity, you’ll want to use a perform like TRUNC as a substitute.

The TRUNC Function in Excel to one decimal place

An instance of TRUNC in Excel.

TRUNC is designed for integer values—entire numbers that lack decimal locations. You can use TRUNC to take away the decimal locations and return entire numbers as a substitute.

If you need to understand how to use this perform, you may try our information to utilizing TRUNC in Excel.

Cleaning Up Data in Microsoft Excel

An Excel spreadsheet doesn’t have to be good—nevertheless it does have to be proper. Thanks to the steps above, it is best to now understand how to truncate textual content in Excel, permitting you to clear up your spreadsheet knowledge.

If you’re working with massive knowledge units, why not break up them into separate sheets? You can then pull the information between sheets in Excel to make it simpler to deal with.

Are you working with colleagues who’ve accessibility points? Don’t neglect to use the Office accessibility checker to see in case your doc wants any adjustments.