Splitting text and numbers from 1 column into separate columns

[al3h]

I have some data that is both text and numbers in the same cell. I would like to split the the data so that the text is in one column and the numbers are in another column.

The numbers are all a fixed length (15 chars) so I know that I can use the following formula = RIGHT(A1,15)

However I am not quite sure how to split the text as the length can vary as as well as the number of words in the string. For example A1 is 1 word with 7 characters but A2 is 2 words, 14 characters long inc space.

A1 Goodwin 000710280740120
A2 Gillette Ridge 000715058510122

B1 Goodwin
C1 000710280740120

B2 Gillette Ridge
C2 000715058510122


Any help would be much appreciated.

Anthony

[avendi]

Try =LEFT(A1,LEN(A1)-15)

[Punnam]

Hi,

This work fine only if u have 0 as starting number after a Space in u r word.

Punnam

[samba_ravi]

=LEFT(A2,MIN(INDEX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2),9^9),0))-1)
Try this in "B2" and
=TRIM(RIGHT(A2,(LEN(A2)-LEN(B2)))) in "C2"

[FDibbins]

B1=LEFT(A1,SEARCH(C1,A1,1)-2)
C1=RIGHT(A1,15)

copied down