software

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!

mtbakerstuIt 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 = quot;quot;
For i = 1 To Len(c)
If InStr(1, quot;abcdefghijklmnopqrstuvwxyz -[]quot;, Mid(c, i, 1),
vbTextCompare) gt; 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 = quot;quot;
For i = 1 To Len(c)
If InStr(1, quot;0123456789quot;, Mid(c, i, 1), vbTextCompare) gt; 0 Then
MyString = MyString Mid(c, i, 1)
End If
Next i
GetNumPart = MyString
End Function

quot;mtbakerstuquot; wrote:

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

Thanks Dave, here are some examples:

129A
44A
44A
44A
VXA44AG
44A
VXB180
169
169
169
D129Are 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(quot;1:100quot;)),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT(quot;1:100quot;)),1))))

entered with ctrl shift amp; 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},A1amp;quot;012345 6789quot;)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

then to get the text just use

=SUBSTITUTE(A1,B1,quot;quot;)

where B1 is the cell with any of the formulas--

Regards,

Peo Sjoblom

nwexcelsolutions.com

quot;mtbakerstuquot; gt; wrote in message oups.com...
gt; Thanks Dave, here are some examples:
gt;
gt; 129A
gt; 44A
gt; 44A
gt; 44A
gt; VXA44AG
gt; 44A
gt; VXB180
gt; 169
gt; 169
gt; 169
gt; D129
gt;