I have a description cell that has variable alphanumeric characters. the
first one to two numbers I would like to copy to another column for the size
of the description
i.e. AA_NAAAa NNN AA N. The number I want is the first number unless the
description shows AANNANN NNNN aAa n, I would want the first two numbers.
these numbers indicate the size of the material. I don't know if a macro
and/or a function would be the best way to go.
--
Sue

This follows the following rule:

If the third character is an underscore, return the fourth character as a
number.
If the third character is a number, return the third and fourth characters
as a number:

=IF(MID(A1,3,1)="_",MID(A1,4,1),MID(A1,3,2))

--
Gary''s Student - gsnu200779


"Sue" wrote:

I have a description cell that has variable alphanumeric characters. the
first one to two numbers I would like to copy to another column for the size
of the description
i.e. AA_NAAAa NNN AA N. The number I want is the first number unless the
description shows AANNANN NNNN aAa n, I would want the first two numbers.
these numbers indicate the size of the material. I don't know if a macro
and/or a function would be the best way to go.
--
Sue

My example wasn't clear enough. if description begins with CV_ show left
digits 4 and 5 i.e.CV 45aa nnn aaa etc, If description begins with VAA shw
left digits 4 and 5 else show 0. i.e. VAA05X05 NNNN aaaann nn etc.
--
Sue


"Gary''s Student" wrote:

This follows the following rule:

If the third character is an underscore, return the fourth character as a
number.
If the third character is a number, return the third and fourth characters
as a number:

=IF(MID(A1,3,1)="_",MID(A1,4,1),MID(A1,3,2))

--
Gary''s Student - gsnu200779


"Sue" wrote:

I have a description cell that has variable alphanumeric characters. the
first one to two numbers I would like to copy to another column for the size
of the description
i.e. AA_NAAAa NNN AA N. The number I want is the first number unless the
description shows AANNANN NNNN aAa n, I would want the first two numbers.
these numbers indicate the size of the material. I don't know if a macro
and/or a function would be the best way to go.
--
Sue

Still not clear. Please post several examples of the input data and along
side the desired numeric output.
--
Gary''s Student - gsnu200779


"Sue" wrote:

My example wasn't clear enough. if description begins with CV_ show left
digits 4 and 5 i.e.CV 45aa nnn aaa etc, If description begins with VAA shw
left digits 4 and 5 else show 0. i.e. VAA05X05 NNNN aaaann nn etc.
--
Sue


"Gary''s Student" wrote:

This follows the following rule:

If the third character is an underscore, return the fourth character as a
number.
If the third character is a number, return the third and fourth characters
as a number:

=IF(MID(A1,3,1)="_",MID(A1,4,1),MID(A1,3,2))

--
Gary''s Student - gsnu200779


"Sue" wrote:

I have a description cell that has variable alphanumeric characters. the
first one to two numbers I would like to copy to another column for the size
of the description
i.e. AA_NAAAa NNN AA N. The number I want is the first number unless the
description shows AANNANN NNNN aAa n, I would want the first two numbers.
these numbers indicate the size of the material. I don't know if a macro
and/or a function would be the best way to go.
--
Sue

This is much more general. The UDF will return the first one or two digits
in any string:

Function numout(r As Range) As Integer
Dim v As String
v = r.Value
numout = 0
j = 1
For i = 1 To Len(v)
dig = Mid(v, i, 1)
If IsNumeric(dig) Then
numout = numout * j + dig
j = j + 9
End If
If j 10 Then Exit Function
Next
End Function

--
Gary''s Student - gsnu200779


"Sue" wrote:

My example wasn't clear enough. if description begins with CV_ show left
digits 4 and 5 i.e.CV 45aa nnn aaa etc, If description begins with VAA shw
left digits 4 and 5 else show 0. i.e. VAA05X05 NNNN aaaann nn etc.
--
Sue


"Gary''s Student" wrote:

This follows the following rule:

If the third character is an underscore, return the fourth character as a
number.
If the third character is a number, return the third and fourth characters
as a number:

=IF(MID(A1,3,1)="_",MID(A1,4,1),MID(A1,3,2))

--
Gary''s Student - gsnu200779


"Sue" wrote:

I have a description cell that has variable alphanumeric characters. the
first one to two numbers I would like to copy to another column for the size
of the description
i.e. AA_NAAAa NNN AA N. The number I want is the first number unless the
description shows AANNANN NNNN aAa n, I would want the first two numbers.
these numbers indicate the size of the material. I don't know if a macro
and/or a function would be the best way to go.
--
Sue

On Mon, 14 Apr 2008 08:27:01 -0700, Sue wrote:

I have a description cell that has variable alphanumeric characters. the
first one to two numbers I would like to copy to another column for the size
of the description
i.e. AA_NAAAa NNN AA N. The number I want is the first number unless the
description shows AANNANN NNNN aAa n, I would want the first two numbers.
these numbers indicate the size of the material. I don't know if a macro
and/or a function would be the best way to go.

If there will be a maximum of two numbers in the first group of numbers, then:

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

If there could be more than two numbers, but you only want to return the first
two, then:

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

or, to return the value as numeric rather than text:

=--LEFT(LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),2 )
--ron