Someone may come along with a formula to replace my use of a User Defined
Function (UDF), which would be a better deal for you, but here's my solution.

First I created a UDF that finds the digits at the left side of the entry
(assumes your data is all like your examples). That UDF is put into a cell.
Then a regular cell formula is put into another cell to get whatever is left
from the original and display it. You'll understand better when you see it.

First, the UDF. Use [Alt]+[F11] to enter the VB Editor and choose Insert |
Module when you get there. Copy the code below and paste it into the empty
module presented to you. Close the VB Editor.

Lets say that 123John Doe is in cell A1, then in B1 put this formula:
=GetDigits(A1)
and in C1 put this formula
=RIGHT(A1,LEN(A1)-LEN(B1))

You should see 123 in B1 and John Doe in C1.

Hope this helps.

"LLG-CN" wrote:

I have a spreadsheet that contains a column that has numbers and text of
varying lengths that I need to split into 2 columns.

Example: 123John Doe
1234Jane Doe

I can't split it using the Text To Columns function because there's nothing
delimited the text and the fixed width doesn't work because the numbers
preceeding the text are varying lengths.

Does anyone know a way to extract the data?
Is there something that extracts numerals vs characters?