Because of importing or dubious data entry, many users have to deal with worksheets that have improperly parsed data. Most commonly, this means that a single cell might contain data that should be divided over several columns. Excel has a few features for parsing this data into several cells.
Text to Columns – Delimited data
If the data in a single cell is divided by spaces, commas, or some other consistent character, then dividing that data into individual cells is fairly simple.
Select the cells you wish to parse. Then click on the Data tab and select Text to Columns under the Data Tools group. This will start the Text to Columns Wizard.
In this example, the address, city, state, and zip code are delimited by commas, which is common for an import, so with the radial button Delimited selected, click Next.
Identify you delimiter by checking the appropriate box or boxes on the left. When the data in the preview window is correct, click Next or Finish.The Next button will allow you to choose the data format for each of the columns. This may not be necessary. Click Finish when you are ready.Your data will be parsed across the columns to the right.
Text to Columns – Fixed Width
In some cases, parsing text from a cell may be easier with a fixed width setting.
Start by selecting the cells you wish to parse. Then under the Data tab in the Data Tools Group, choose Text to Columns. In the Wizard dialogue box choose the radial button Fixed Width and choose Next. Use the preview window to select the width of your data by dragging the line to the proper position. Click Next to identify the data format or Finish.
Extracting data from the center of a text string
In Excel the MID function allows a user to extract data from the center of a cell. The syntax for this formula is:
=MID({cell reference}, {order number for the first character desired}, {number of characters to include})
=MID(…) calls up the formula.
{Cell reference} is the cell that has the data.
{order number for the first character desired} refers to the character number for the first letter desired. In the example above, this number is 7, referring to the 7 characters in the word AMOUNT_.
{number of characters to include} refers to the number of characters that should be extracted. In the case of the example, this number is 5, as in the 5 characters in $2304.
