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 data>text to columns>delimited>>>>

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"ACCAguy" <(E-Mail Removed)> wrote in message
news:158DA626-C9C3-4833-A307-(E-Mail Removed)...
> 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 <bli...@gazeta.pl> 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&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--
__________________________________
HTH

Bob

"ACCAguy" <(E-Mail Removed)> wrote in message
news:158DA626-C9C3-4833-A307-(E-Mail Removed)...
> 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
(E-Mail Removed)
"Jarek Kujawa" <(E-Mail Removed)> wrote in message
news:b5f8730c-e89e-492f-89d6-(E-Mail Removed)...
> Don,
> doesn't the OP need to divide 123ABC into sth. like 123 (in one cell)
> and ABC (in another)?