How to insert space between number and text in cells in Excel?

We can apply formula to insert space between number and text easily in Excel. Please do as follows:

1. Select a blank cell, enter the formula =TRIM(REPLACE(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&"1234567890")),0," ")) (A2 is the cell where you will insert space between number and text) into it, and press the Enter key. See screenshot:

2. And then drag this cell’s Fill Handle to the range as you need. See screenshot:

Note: This formula can only add a space before the first number. For example, this formula will change A5B6C8D6E0F9 to A 5B6C8D6E0F9.

If you want to separate numbers and text by adding a space before each number, you can try Kutools for Excel’s Add Text utility. 

1. Select the cells that you will add space before each number, and click Kutools > Text > Add Text. See screenshot:

2. In the opening Add Text dialog box, please (see screenshot above):
(1) Type a space into the Text box;
(2) Check the Only add to option and select 1st character is number from the below drop down list;

3. Click the Ok button. And then you will see space is added before each number. See screenshot:

Batch Separate text and numbers from one cell/column into different columns/rows

Kutools for Excel enhances its Split Cells utility and supports to batch separate all text characters and numbers of one cell/column into two columns/rows.