Can anyone help - I am sure there is a easy way to do this

[ACCAguy]

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

 

[Don Guillett]

ALWAYS best to try to describe your problem in the subject line......
Try data>text to columns>delimited>>>>

 

[Jarek Kujawa]

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

 

[Jarek Kujawa]

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

 

[Jarek Kujawa]

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

 

[Bob Phillips]

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

 

[Don Guillett]

Yes, I need to read more CAREFULLY.