Read Them in as Fixed Width Columns, and Specify Exactly the Size of Each Column

This chapter is from the book 

Using Text to Columns to Separate Data in a Single Column

Information, Information Tools, Text to Columns can be used to split up data in a single column into multiple columns, such as if you have total names in one cavalcade and need a cavalcade with commencement names and a column with last names. When yous select the control, a wizard dialog box opens to help you through the process. In step 1 of the wizard, select whether the text is Delimited or Stock-still Width (run into the next sections for definitions of Delimited and Fixed Width). In step ii, you provide more details on how you desire the text separated. In step 3, you tell Excel the basic formatting to use to each cavalcade.

If yous take data in the columns to the right of the column you are separating, Excel overwrites the data. Be certain to insert plenty blank columns to not overwrite your existing data before beginning Text to Columns. Meet the section "Inserting an Entire Row & Column" in affiliate 2, "Working with Workbooks, Sheets, Rows, Columns, and Cells," for instructions on how to insert columns.

Working with Delimited Text

Delimited text is text that has some character, such as a comma, tab, or space, separating each group of words that you want placed into its ain column. To dissever delimited text into multiple columns, follow these steps:

1. Highlight the range of text to exist separated.
2. Become to Data, Data Tools, Text to Columns. The Convert Text to Columns Wizard opens.
3. Select Delimited from stride 1 of the wizard, as shown in Effigy 3.6, and click Next.

   

   Effigy 3.6. Select the Delimited selection to split up text joined by delimiters, such as commas, spaces, or tabs.

4. Select one or more delimiters used past the grouped text, every bit in Figure 3.vii, and click Adjacent.

   

   Effigy 3.7. Select the Delimited option to apply the comma as a delimiter between the urban center, country, and Goose egg Code.

   If y'all need more ane delimiter but one of the delimiters is used normally in the text, such as the infinite between city names and the infinite between a state and ZIP Code (Sioux Falls, SD 57057), consider running Text to Columns twice: once to separate the city (Sioux Falls) from the state Aught Code (SD 57057) and again to separate the state and Zilch Code.

5. For each cavalcade of data, select the data format. For example, if you take a column of ZIP Codes, yous need to set the format as Text so whatsoever leading zeros are non lost. But exist warned—setting a cavalcade to Text prevents Excel from properly identifying formulas entered into that cavalcade.
6. Click End. The text is separated, as shown in Figure 3.8.

   

   Figure 3.eight. Using Text to Columns with a comma delimiter separated the city from the state and Cipher Lawmaking. Run the wizard once more on the state ZIP Lawmaking column with a space every bit a delimiter to split upwards that information.

Working with Fixed-Width Text

Fixed-width text describes text where each group is a set number of characters. You can depict a line down all the records to divide all the groups, as shown in Figure 3.9. If your text doesn't look like information technology'due south fixed width, endeavor changing the font to a fixed-width font, such as Courier. It'due south possible that it'south fixed-width text in disguise.

Figure 3.ix. Use the Stock-still Width option when each group in the information has a fixed number of characters.

To split fixed-width text into multiple columns, follow these steps:

1. Highlight the range of cells that includes text to exist separated.
2. Go to Data, Text to Columns.
3. Select Fixed Width from step 1 of the wizard and click Next.
4. Excel will guess at where the column breaks should go, as shown in Figure three.9. You can move a pause by clicking and dragging it to where yous want it, insert a new break by clicking where it should be, or remove a suspension by double-clicking it. Click Next.

   Don't worry virtually leading spaces—Excel will remove them for you.

5. For each column of data, select the data format. For case, if you have a column of Nothing Codes, you need to set the format as Text so any leading zeros are not lost, as shown in Figure 3.ten. But exist warned—setting a column to Text prevents Excel from properly identifying formulas entered into that column.

   

   Figure 3.x. In stride iii of the wizard, set the format of each cavalcade. If you lot accept numeric text, such as ZIP Codes, make sure you configure the wizard to treat the column as text and so you don't lose any leading zeros.

6. Click Finish.

murraycouldayse.blogspot.com

Source: https://www.informit.com/articles/article.aspx?p=2027553&seqNum=5