Can anyone help with a formula to separate a field containing numbers and
letters into numbers per below? I only know how to use for eg =left(X,x) but
that isn't helpful as different fields have different number of numeric
charcters.


123ABC - 123
1234DEF - 1234
12GH - 12
--
ACCAguy

ALWAYS best to try to describe your problem in the subject line......
Try datatext to columnsdelimited

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ACCAguy" wrote in message
...
Can anyone help with a formula to separate a field containing numbers and
letters into numbers per below? I only know how to use for eg =left(X,x)
but
that isn't helpful as different fields have different number of numeric
charcters.


123ABC - 123
1234DEF - 1234
12GH - 12
--
ACCAguy

Don,
doesn't the OP need to divide 123ABC into sth. like 123 (in one cell)
and ABC (in another)?

you may try to use this macro
first select your cells (123ABC etc.) - the results will be stored
one column to the right

Sub wydziel()
Dim cell As Range
Dim tekst As String
Dim i As Integer

For Each cell In Selection
tekst = vbNullString
For i = 1 To Len(cell)
If IsNumeric(Mid(cell, i, 1)) Then
tekst = tekst & Mid(cell, i, 1)
Else:
Exit For
End If
Next i
cell.Offset(0, 1) = tekst
Next cell
End Sub

On 29 Sie, 21:51, Jarek Kujawa wrote:
Don,
doesn't the OP need to divide 123ABC into sth. like 123 (in one cell)
and ABC (in another)?

=LOOKUP(10^10,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1)))))

--
__________________________________
HTH

Bob

"ACCAguy" wrote in message
...
Can anyone help with a formula to separate a field containing numbers and
letters into numbers per below? I only know how to use for eg =left(X,x)
but
that isn't helpful as different fields have different number of numeric
charcters.


123ABC - 123
1234DEF - 1234
12GH - 12
--
ACCAguy

Yes, I need to read more CAREFULLY.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jarek Kujawa" wrote in message
...
Don,
doesn't the OP need to divide 123ABC into sth. like 123 (in one cell)
and ABC (in another)?