This wikiHow teaches you how to crop data views in Microsoft Excel. Before cutting it, the complete data that has not been trimmed needs to be entered into an Excel spreadsheet first.
Step
Method 1 of 3: Cropping Text Using “LEFT” and “RIGHT” Formulas
Step 1. Open Microsoft Excel
If you have a document with data already saved, double-click the document to open it. Otherwise, you will need to open a new workbook or workbook and enter data first at this stage.
Step 2. Select the box you want to use to display the data
This method is suitable for use when the text is already stored in a spreadsheet.
Keep in mind that the selected box is a different box from the box containing the target text
Step 3. Type the formula “LEFT” or “RIGHT” into the selected box
The “LEFT” and “RIGHT” formulas are designed for the same function. However, the “LEFT” formula displays characters from the left side of the text box, while the “RIGHT” formula displays characters from the right side of the box. The formula that needs to be entered is "=DIRECTION(box name, number of characters to display)" without the quotes. As an example:
- Formula =LEFT(A3, 6) will display the first six characters of the text in the A3 box. If box A3 contains the text "Cats are very attractive", the trimmed text will display as "Cat" in the selected box (in this case, the box to which you added the formula).
- Formula =RIGHT(B2, 5) will display the last five characters of the text in box B2. If box B2 contains the text "I love wikiHow", the trimmed text will display as "kiHow" in the selected box.
- Keep in mind that spaces count as characters.
Step 4. Press Enter after you finish entering the formula
The selected box will automatically be filled with the snippet of text.
Method 2 of 3: Trimming Text Using the “MID” Formula
Step 1. Select the box you want to use to display the data
This box must be different from the box containing the target text.
Enter the data first into the Excel spreadsheet if you haven't already
Step 2. Type the formula “MID” into the selected box
The “MID” formula takes the center of the text in the source box, without including the beginning and end. To enter the “MID” formula, type "=MID(box name, initial character number, number of characters to display)" without the quotes. As an example:
- Formula =MID(A1, 3, 3) will display three characters of the text in box A1, starting with the third character of the text (from the left). If box A1 contains the text "racing car", the trimmed text will be displayed as "bill" in the selected destination box.
- Meanwhile, the formula =MID(B3, 4, 8) will display eight characters of text in box B3, starting at character four from the left. If box B3 contains the text "bananas are not human", the retrieved text will be displayed as "opened" in the selected destination box.
Step 3. Press Enter after you finish entering the formula
The snippet of text will be added to the selected box.
Method 3 of 3: Split Text Into Multiple Columns
Step 1. Select the box with the text you want to share
The selected box contains text with more characters than the available space.
Step 2. Click Data
This option is in the toolbar at the top of the Excel page.
Step 3. Select Text to Columns
This option is in the " Data Tools " section of the " Data " tab.
This function divides the contents of one of the Excel boxes into separate columns
Step 4. Select Fixed Width
After clicking " Text to Columns ”, the " Convert Text to Columns Wizard Step 1 of 3 " window will be displayed. This window has two options: " Delimited " and " Fixed Width ". The “Delimited” option indicates characters such as tabs or commas to separate each text box. You usually need to select “Delimited” when importing data from other applications (eg databases). The “Fixed Width” option indicates that the boxes are aligned on a column that has a space between each box.
Step 5. Click Next
This new window displays three options. If you want to create a line delimiter, click the desired position of the delimiter or text separator. If you want to remove the delimiter or separator, double-click the line. To adjust it, click and drag a line on the data.
Step 6. Click Next
This window has several options, namely " General ", " Text ", " Date ", and " Do not import column (skip) ". You can skip the options on this page, unless you want to change the original format of the text to a different format.
Step 7. Click Finish
Your text will now be divided into two or more columns.
