Buzz
Today, Mytour will guide you on how to condense the display of data in Microsoft Excel. Before proceeding, ensure that the complete, unshortened data is already entered into Excel.
Steps
Shorten Text Using LEFT and RIGHT Formulas
Launch Microsoft Excel. If you already have a document with pre-entered data, simply double-click to open it; if not, you need to open a new spreadsheet and input the data immediately.
Select the cell where you want the shortened text to appear. This method works best with text already entered in the spreadsheet.
- Note: This cell must be different from the one displaying the original text.
Enter the LEFT or RIGHT formula into the selected cell. Both LEFT and RIGHT formulas operate on the same principle, but LEFT displays characters from the left side of the text in the cell, while RIGHT does the opposite. The formula will be "=DIRECTION(cell name, number of characters to display)". Note: Do not include quotation marks. See the examples below:
- =LEFT(A3, 8) will display the first 8 characters from the left in cell A3. If the text in cell A3 is "Quantity of goods," the shortened text in the selected cell will show "Quantity."
- =RIGHT(B2, 5) will display the last 5 characters in cell B2. If cell B2 contains "I love Mytour," the shortened text in the selected cell will be "kiHow."
- Note: Spaces are also counted as characters.
Press Enter after completing the formula. The specified cell will automatically display the shortened text.
Shorten Text Using the MID Formula
Select the cell where you want the shortened text to appear. This cell must be different from the one containing the target text.
- If the spreadsheet is empty, you need to add data first.
Enter the MID formula into the selected cell. MID will trim characters from the beginning and end of the target text. To set up the MID formula, input "=MID(cell name, starting character number, number of characters to display)". Note: Do not include quotation marks. See the examples below:
- =MID(A1, 3, 3) will display 3 characters, starting from the third character from the left in the text of cell A1. If the text in cell A1 is "race car," the shortened content in the selected cell will show "ce ".
- Similarly, =MID(B3, 4, 8) will display 8 characters, starting from the fourth character from the left in the text of cell B3. If the content in cell B3 is "COVID-19 pandemic," the selected cell will display the shortened text "ID-19 pa".
Press Enter after completing the formula. The specified cell will automatically display the shortened text.
Split Text into Multiple Columns
Select the cell you want to split. This is the cell containing too many characters to display fully within its space.
Click on Data in the top toolbar of Excel.
Select Text to Columns. This option is located in the "Data Tools" section of the Data tab.
- This feature will split the content of the selected cell into multiple separate columns.
Choose Fixed Width. After clicking Text to Columns, the "Convert Text to Columns Wizard Step 1 of 3" window will appear. Here, there are two options: "Delimited" and "Fixed Width." Delimited splits characters like tabs or commas into separate fields. This option is typically used when importing data from another application, such as a database. Meanwhile, Fixed Width organizes fields into columns separated by a fixed space.
Click Next. This window has 3 options. If you want to create a line break, click where you want the text to split. To remove a line break, double-click on it. You can also adjust by clicking and dragging the line to a different position in the data.
Click Next. This window offers options like "General," "Text," "Date," and "Do not import column (skip)." Unless you want to change the cell format to something other than the default, you can skip this page.
Click Finish. The text will be split into two or more cells.
