Does anyone know of an efficient way to separate letters and numbers
which exist in one cell into different cells for each respective part?
It has been suggested to me that there should be a way to do this in
Excel. I'm using Excel 2000.

Thanks Everyone!

mtbakerstu

It can be done, but the solution you use will depend on the layout of
the data, how the alphas and numerics are mixed in the string. Please
post some representative examples.

Enter these two UDF's in a Standard module:

Public Function GetTextPart(c) As String
Dim i As Integer
Dim MyString As String

'Templatebuilder
'Returning text value from string'
MyString = ""
For i = 1 To Len(c)
If InStr(1, "abcdefghijklmnopqrstuvwxyz -[]", Mid(c, i, 1),
vbTextCompare) 0 Then
MyString = MyString + Mid(c, i, 1)
End If
Next i
GetTextPart = MyString
End Function

Public Function GetNumPart(c) As String
Dim i As Integer
Dim MyString As String

'Templatebuilder
'Returning numeric value from string'
MyString = ""
For i = 1 To Len(c)
If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) 0 Then
MyString = MyString + Mid(c, i, 1)
End If
Next i
GetNumPart = MyString
End Function

"mtbakerstu" wrote:

Does anyone know of an efficient way to separate letters and numbers
which exist in one cell into different cells for each respective part?
It has been suggested to me that there should be a way to do this in
Excel. I'm using Excel 2000.

Thanks Everyone!

mtbakerstu

Thanks Dave, here are some examples:

129A
44A
44A
44A
VXA44AG
44A
VXB180
169
169
169
D129

Are the numbers always together like in your example and not like 44A55B66?
If so this somewhat ugly array formula will work

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

another one by Domenic I believe

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

then to get the text just use

=SUBSTITUTE(A1,B1,"")

where B1 is the cell with any of the formulas





--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"mtbakerstu" wrote in message
oups.com...
Thanks Dave, here are some examples:

129A
44A
44A
44A
VXA44AG
44A
VXB180
169
169
169
D129