R
RH
Can ICF-210-3-10a be converted into a number?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
easily:
=MID(A1,5,3) returns the 210
=MID(A1,9,1) returns the 3
=MID(A1,11,2) returns the 10
=MID(A1,5,3) returns the 210
=MID(A1,9,1) returns the 3
=MID(A1,11,2) returns the 10
R
RH
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?
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?
G
Gary''s Student
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.
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.
G
Gord Dibben
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
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
R
RH
This is great - Thanks so much.
Gary''s Student said: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.