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:

Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more…     Free trial

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.