Knowledge Library

3 Practical Uses of Dividing Text into Excel Table

On Microsoft Excel, You can take the text in one or more cells, and split it into multiple cells using the [Text to Columns] function on Microsoft Excel.

Splitting Text with Delimiters vs. Fixed Width

You can use Text to Columns in two different modes: fixed-width and delimited. While delimited might be more useful, fixed-width is a bit easier.

Delimited vs. Fixed Width

Delimited: Takes place when Excel sees certain characters. It might split the text at every comma, tab, or space that it finds.

Fixed Width: Excel splits the text at specific character counts. So it might split the text at desired character counts. For example, you may split your text at the 5th, 15th, or 26th character count.

Using the Text To Columns Function

1.Delimited

– Insert a new blank column to the right of the cell range you want to split as split destination.

*By inserting a blank column as split text destination, it prevents the split text from overwriting your original data.

– Select the range of cells that contains the text you want to split.

– Select [Data] > [Text to Columns].

– In the [Convert Text to Columns Wizard], select [Delimited] >[ Next].

– Select the Delimiters for your data. For example, Comma and Space. You may also insert a specific delimiter in the [Others] option.

– You can see a preview of your data in the Data preview window.

– Select [Finish].

2.Fixed Width

– Insert a new blank column to the right of the cell range you want to split as split destination.

*By inserting a blank column as split text destination, it prevents the split text from overwriting your original data.

– Select the range of cells that contains the text you want to split.

– Select [Data] > [Text to Columns].

– In the [Convert Text to Columns Wizard], select [Fixed Width] >[ Next].

-Set your fixed width to split your text

1. Click on the preview to add a line (Excel will split your text at each line).
2. Double-click on a line to remove it.
3. Click and drag a line to move it.

– Preview your data in the preview window.

– Select [Finish].

在Microsoft Excel，您可以擷取一個或多個儲存格內的文字，然後使用 [資料剖析精靈] 將它分割至多個儲存格中。

分隔符號及固定寬度

您可以在兩種不同的模式下使用資料剖析精靈：分隔符號和固定寬度。儘管分隔符號可能更有用，固定寬度會比較容易操作。

分隔符號和固定寬度的分別

分隔符號：如果在原始資料上有特定的分隔符例如逗號、分號、空白、tab鍵、或其他符號區隔，就可以選擇這個模式。

固定寬度：使用這模式，Excel會以固定的字數寬度分割資料。因此，它可以按所需的字符數分割資料。例如，您可以按第5、15或26個字數分割。

使用資料剖析精靈

1. 分隔符號

– 插入一個新的空白列作為分隔後的目標地。

*必須保留足夠的空白列，否則分隔後的數據將會取代原有的數據。

– 選取內含要分割之文字的儲存格。

– 選取 [資料] > [資料剖析]。

– 選取 [資料剖析精靈] 中的 [分隔符號] > [下一步]。

– 選取資料的 [分隔符號]。例如 [逗號] 和 [空格]。你亦可以在 [其他] 選項中輸入特定的符號。

– 您可以在 [預覽分欄結果] 視窗中看到您資料的預覽。

– 選取 [完成]。

2. 固定寬度