separating imported data into 2 cell format

[Berby]

I have imported a free text catalogue which needs to be converted into a
catalogue. The description and part number are in the same cell. The part
number appears at the end of the string and is 7 digits long. The part number
appears in only some of the cells in the column but i want it to only copy
the part number into a new cell.
Also where there is a cell with no part number on the end is the end of the
description for the part on the line(s) above. how can i merge all the text
in the cells between these points whilst still keeping the description tied
to the correct part number?!
example of this is as follows:

Breathing hose, P, 1.5 m (4.9 ft) 2165961
Silicone breathing hose (pediatric)
with 10/22 (0.4/0.9) standard sleeves.

which i want to look like this:

2165961 Breathing hose, P, 1.5 m (4.9 ft), Silicone breathing hose
(pediatric) with 10/22 (0.4/0.9) standard sleeves.

 

[galimi]

Assuming your data is in cell D1, you could use the following formula:

=RIGHT(D1,7) & " " & LEFT(D1,LEN(D1)-7)