Can ICF-210-3-10a be converted into a number?

easily:

=MID(A1,5,3) returns the 210
=MID(A1,9,1) returns the 3
=MID(A1,11,2) returns the 10

--
Gary''s Student - gsnu200817

Within a named cell range I'm having my formula look-up the model numbers
ICF-210-3-10a, BFF-22-10b. Another cell gathers the look-up information and
creates a calculation. I'm not sure how to turn ICF-210-3-10a into the
number. Could you help?

"Gary''s Student" wrote:

> easily:
>
> =MID(A1,5,3) returns the 210
> =MID(A1,9,1) returns the 3
> =MID(A1,11,2) returns the 10
>
> --
> Gary''s Student - gsnu200817

We will convert using a VLOOKUP Table:

Say we have a list of model ids in a column, say column A:

Model
QHO-319-78-78a
ACG-221-42-62r
LZQ-11-37-15g
MCB-43-13-83l
CEJ-101-61-32a
KOR-121-39-68a
UQJ-474-35-94x
DHF-130-66-98z
JYM-263-23-11a
JCD-432-19-40k
EQB-489-81-75x
XGJ-237-47-90j
ZOA-47-55-69u
XGV-185-25-23c
SWE-258-63-68d
XIN-257-10-29o
OXI-217-51-21b
LFU-286-34-79l
CAO-356-99-27d
DHC-280-76-16x
BEJ-195-84-78l
TXT-419-83-11s
FWZ-409-93-51t
MYW-325-43-64v
QMD-180-28-12l
WYF-155-33-57y
OEP-128-85-35g
JNE-308-82-54e
HVY-68-67-44k

we assume that the model ids are unique. In B1 thru B29 enter 1 thru 29.

These are the numbers associated with each text value. Use VLOOKUP() to get
the number from a cell containing the model id.

--
Gary''s Student - gsnu200817


"RH" wrote:

> Within a named cell range I'm having my formula look-up the model numbers
> ICF-210-3-10a, BFF-22-10b. Another cell gathers the look-up information and
> creates a calculation. I'm not sure how to turn ICF-210-3-10a into the
> number. Could you help?
>
> "Gary''s Student" wrote:
>
> > easily:
> >
> > =MID(A1,5,3) returns the 210
> > =MID(A1,9,1) returns the 3
> > =MID(A1,11,2) returns the 10
> >
> > --
> > Gary''s Student - gsnu200817

Easily done using a UDF

Function RemAlpha(str As String) As String
'Remove all but numerics from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

Copy the UDF to a workbook module then using a helper cell enter

=RemAlpha(cellref)


Gord Dibben MS Excel MVP

On Mon, 1 Dec 2008 06:39:02 -0800, RH <(E-Mail Removed)> wrote:

>Can ICF-210-3-10a be converted into a number?

This is great - Thanks so much.

"Gary''s Student" wrote:

> We will convert using a VLOOKUP Table:
>
> Say we have a list of model ids in a column, say column A:
>
> Model
> QHO-319-78-78a
> ACG-221-42-62r
> LZQ-11-37-15g
> MCB-43-13-83l
> CEJ-101-61-32a
> KOR-121-39-68a
> UQJ-474-35-94x
> DHF-130-66-98z
> JYM-263-23-11a
> JCD-432-19-40k
> EQB-489-81-75x
> XGJ-237-47-90j
> ZOA-47-55-69u
> XGV-185-25-23c
> SWE-258-63-68d
> XIN-257-10-29o
> OXI-217-51-21b
> LFU-286-34-79l
> CAO-356-99-27d
> DHC-280-76-16x
> BEJ-195-84-78l
> TXT-419-83-11s
> FWZ-409-93-51t
> MYW-325-43-64v
> QMD-180-28-12l
> WYF-155-33-57y
> OEP-128-85-35g
> JNE-308-82-54e
> HVY-68-67-44k
>
> we assume that the model ids are unique. In B1 thru B29 enter 1 thru 29.
>
> These are the numbers associated with each text value. Use VLOOKUP() to get
> the number from a cell containing the model id.
>
> --
> Gary''s Student - gsnu200817
>
>
> "RH" wrote:
>
> > Within a named cell range I'm having my formula look-up the model numbers
> > ICF-210-3-10a, BFF-22-10b. Another cell gathers the look-up information and
> > creates a calculation. I'm not sure how to turn ICF-210-3-10a into the
> > number. Could you help?
> >
> > "Gary''s Student" wrote:
> >
> > > easily:
> > >
> > > =MID(A1,5,3) returns the 210
> > > =MID(A1,9,1) returns the 3
> > > =MID(A1,11,2) returns the 10
> > >
> > > --
> > > Gary''s Student - gsnu200817