This article shows how to truncate the data present in a Microsoft Excel sheet. In order to proceed, all the data, in their original form, must already be in the worksheet. Read on to find out how to truncate text in Excel.
Steps
Method 1 of 3: Use the LEFT and RIGHT functions
Step 1. Launch Microsoft Excel
If you need to change the data in an Excel document, double-click it to open it. If not, you will need to create a new workbook and enter the data you are going to work with.
Step 2. Select the cell where you want the truncated text to be displayed
This method is very useful if the text has already been inserted into the Excel sheet.
Note: the selected cell must necessarily be different from the cell containing the original data
Step 3. Type the "LEFT" or "RIGHT" function into the chosen cell
Both functions are based on the same principle: the first shows the text starting from the left and moving to the right by the indicated number of characters, while the second does exactly the same thing, but starting from the right side and moving to the left. The formula to use is "= [LEFT or RIGHT] ([Cell], [number _characters_to_show])" (without quotes). For instance:
- = LEFT (A3, 6): shows the first 6 characters contained within cell A3. If the text in that cell were "Cats are better", the text displayed by the formula would be "I cats".
- = RIGHT (B2, 5): this formula shows the last 5 characters present in cell B2. Assuming that it contains the string "I love wikiHow", the text displayed after the truncation would be "kiHow".
- Remember that blanks are counted like any other character.
Step 4. After completing the creation of the formula, press the Enter key
The selected cell will be automatically filled with the text resulting from the truncation.
Method 2 of 3: Use the STRING. EXTRACT function
Step 1. Select the cell where you want the truncated text to be displayed
Remember that the selected cell must necessarily be different from the cell containing the original data.
If the Excel document is empty, you will need to fill in the data before you can do the truncation
Step 2. Type the STRING. EXTRACT formula into the selected cell
This function shows the portion of text between the start and end points. The formula to use is "= STRING. EXTRACT ([Cell], [Number_of_characters], [Number_of_characters_to_show])" (without quotes). For instance:
- = EXTRACT STRING (A1, 3, 3): this formula shows the 3 characters present in cell A1 starting from the third character on the left. So, if cell A1 contains the string "Formula 1", the text truncated and displayed in the chosen cell would be "rmu".
- = EXTRACT STRING (B3, 4, 8): in this case the first 8 characters of cell B3 are shown starting from the fourth. Assuming that cell B3 contains the text "bananas are not people", the text displayed in the cell where the formula was entered would be "bananas n".
Step 3. After completing the creation of the formula, press the Enter key
The selected cell will be automatically filled with the text resulting from the truncation.
Method 3 of 3: Split Text into Multiple Columns
Step 1. Select the cell which contains the text to split
This could be a cell that has a multi-word string entered.
Step 2. Select the Data tab
It is located on the Excel ribbon located at the top of the window.
Step 3. Choose the Text to Columns function
It is located within the "Data Tools" group of the "Data" tab.
This function splits the contents of the indicated Excel cell into multiple separate columns
Step 4. Choose the Fixed Width option
After pressing the button "Text in columns", the "Convert Text to Columns Wizard - Step 1 of 3" dialog box appears. On this first page you will have two options: "Delimited" and "Fixed width". The first means that the words that make up the text to be divided are delimited by a special character, for example a tab, a semicolon or a space. Normally this option is used when importing data extracted from another source, for example a database. The "Fixed width" option indicates instead that the fields that make up the text are contiguous and all have the same length.
Step 5. Press the Next button
The second step of the Convert Text to Columns wizard offers you the ability to perform three actions. The first is to create a new break line to delimit a new field: just click on the position where you want to insert the text separator. The second is to delete an existing break line: just select the break line to be deleted with a double click of the mouse. The third is to move an existing break line to a new position: simply select it with the mouse and drag it to the new desired position.
Step 6. Press the Next button
The last screen of the wizard shows the options for formatting the data: "General", "Text", "Date" and "Don't import this column (skip)". Unless you want to change Excel's default formatting so that the processed data takes a different format, you can skip this last step.
Step 7. Press the Finish button
The indicated text should be split into two or more cells, depending on the content.
