How to Truncate Text in Excel

Method 1 of 3:

Truncating Text Using the LEFT and RIGHT Formulas

Open Microsoft Excel. If you have an existing document with your data already entered, you can double-click it to open it; otherwise, you'll need to open a new workbook and enter your data now.
Select the cell where you want the truncated text to appear. This method is useful for text that you already have in your spreadsheet.
1. Note that this cell must be different than the cell in which your target text appears.
Type the LEFT or RIGHT formula into your selected cell. The LEFT and RIGHT formula are built on the same premise, though the LEFT formula displays characters from the left side of your cell's text and the RIGHT formula displays characters from the right. The formula is "=DIRECTION(Cell Name, Number of characters to display)" without the quotation marks. For example:^[1]
1. =LEFT(A3, 6) displays the first six characters in cell A3. If the text in A3 says "Cats are better", the truncated text will read "Cats a" in your selected cell.
2. =RIGHT(B2, 5) displays the last 5 characters in cell B2. If the text in B2 says "I love wikiHow", the truncated text will read "kiHow" in your selected cell.
3. Keep in mind that spaces count as characters.
Press Enter when the formula is complete. Your selected cell will automatically fill with the truncated text.

Method 2 of 3:

Truncating Text Using the MID Formula

Select the cell where you want the truncated text to appear. This cell must be different than the cell in which your target text appears.
1. If you haven't already added your data to Excel, you'll need to do so first.
Type the MID formula into your selected cell. MID chops characters off of the beginning and end of your selected cell's text. To set up the MID formula, you type "=MID(Cell Name, Starting Character Number, Number of characters to display)" without the quotation marks. For example:
1. =MID(A1, 3, 3) displays three characters from cell A1, the first of which is the third character from the left in the text. If A1's text says "racecar", the truncated text will read "cec" in your selected cell.
2. Similarly, =MID(B3, 4, 8) displays eight characters from cell B3, starting with the fourth character from the left. If B3's text says "bananas aren't people", the truncated text will read "anas are" in your selected cell.
Press Enter when the formula is complete. This will add the truncated text to your selected cell.

Method 3 of 3:

Splitting Text Into Multiple Columns

Select the cell you wish to split up. This should be a cell that has more characters than space.
Click Data. It's in the toolbar at the top of your Excel page.
Select Text to Columns. You'll find this option in the "Data Tools" section of the Data tab.
1. This function divides the cell's contents of one Excel cell into separate columns.
Select Fixed Width. After clicking on Text to Columns window will pop up, called "Convert Text to Columns Wizard Step 1 of 3." The window will have two selections: "Delimited" and "Fixed Width." Delimited means that characters, such as tabs or commas will divide each field. You will usually select delimited when you're importing data from another application, such as a database. The fixed width option means that the fields are lined up in columns that have spaces between the individual field.
Click Next. This window shows three options. If you want to create a break line, click on the position where you want the text to break. If you want to delete the break line, double click on the line. To adjust the line, click and drag it around the data.
Click Next. This window has several options, "General", "Text," "Date" and "Do not import column (skip)." Unless you want to force your cell's formatting to do something different than its natural state, you can skip this page.
Click Finish. Your text should now be divided between two or more cells.

Lesley Montoya

Update 05 March 2020

You should read it

May be interested

How to rotate text in Excel
excel can also rotate text similarly when rotating text on word to match the layout of the table, as well as make a difference to the content.
Separate text from strings in Excel, for example, and how to do it
separate text from strings in excel, for example, and how to do it. in excel, there are 3 basic functions that help you separate text from strings in excel: left, right, mid. if you are looking to separate words from a string, please refer to the article
How to strikethrough text in Word, write strikethrough text in Word and Excel
strikethrough in word and excel helps you highlight the text you want to edit or replace. the following article will guide you on how to create strikethrough text in word and excel and remove strikethrough text in existing documents.
The Text function converts a numeric value into text in Excel
in the process of working with excel, when the requirements of the job you need to convert from the number format to text format with the specified format suitable for the purpose of use. the text function will help you do that.
How to automatically wrap lines in Excel (Wrap Text in Excel)
the line break in excel is not as simple as pressing enter as in ms word software. today, dexterity software will guide you to the warp text tool to automatically wrap lines that match the width of the column!
Inserting images into Excel without covering text is SIMPLE and QUICK
inserting images into excel without covering text is the trick you are looking for? tipsmake has detailed instructions on how to do this in the article below
Instructions on how to create flashing text in Excel
want to highlight information in excel? creating flashing text will help attract attention, and it's very simple to do.
How to Shorten Text in Excel
today's tipsmake will show you how to shorten the appearance of data in microsoft excel. before proceeding, the full, unreduced data needs to be entered in excel.
3 ways down the line in Excel, line break, down row in 1 Excel cell
there will be 3 ways downstream in excel, suitable for each case of content entry. please follow the downstream instructions in excel below for details.
Guide to full Excel 2016 (Part 7): Format spreadsheet data
if you have seen the article about microsoft word, then you also have some basic knowledge about text alignment. please refer to the article of formatting spreadsheet data in excel 2016 in this article!