Convert Text to Number

[RH]

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

 

[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

 

[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?

 

[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.

 

[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

 

[RH]

This is great - Thanks so much.

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.