Convert text to number automatically

[Matt]

I am trying to compare drawing revisions from 2 different sources but am
having difficulty due to the sources having either text or number formats.
Sheet 3 (PH1) is the 1st source and is from another excel spreadsheet - no
problem. All formats are GENERAL.
A B
1 Drwg Rev
2 0001 0
3 0002 0
4 0003 A
5 0004 A
Sheet 2 (PH2) is the 2nd source and is an export from an external document
management program that I would like to import weekly. I want to use this
sheet exclusively for pasting export data only (ie do not do anything else
with it here). All formats are TEXT.
A B
1 Drwg Rev
2 0001 0
3 0002 1
4 0003 B
5 0004 0
Sheet 1 (COMPARISON) is where I would like to compare the documents with the
desired results as shown in column D where Y=YES;
A B C D
1 Drwg PH1 PH2 Change
2 0001 0 0
3 0002 0 1 Y
4 0003 A B Y
5 0004 A 0 Y
Formulas entered into sheet 1 are as follows (row 2 shown only).
A2=IF(ISTEXT(PH2!A2),PH2!A2,"")
B2=IF(ISERROR(VLOOKUP(A2,PH1!$A$2:$B$5,2,0)),"",VLOOKUP(A2,PH1!$A$2:$B$5,2,0))
C2=VLOOKUP(A2,PH2!$A$2:$B$5,2,0)
Note that PH2 is the most current sheet and contains all drawings whereas
PH1 does not (hence different formulas above). All revisions in column C
would be either be equal to or greater than column B. Note that revisions
are alphabetical then numerical ie A,B,C then 0,1,2.
Given the text/number format compatability issue, and the comparison between
letters and numbers, I am sure that a VB code would do what I'm looking for
(but I would feel more comfortable with a formula if possible).

Any ideas? Thanks in advance.

 

[Bob Phillips]

Try

B2:
=IF(ISERROR(VLOOKUP(--A2,PH1!$A$2:$B$5,2,0)),"",VLOOKUP(--A2,PH1!$A$2:$B$5,2,0))
C2:
=IF(ISNUMBER(--VLOOKUP(A2,PH2!$A$2:$B$5,2,0)),--VLOOKUP(A2,PH2!$A$2:$B$5,2,0),VLOOKUP(A2,PH2!$A$2:$B$5,2,0))

 

[Matt]

Thanks Bob. That converts the text to number format. Any thoughts on what
formula I would use in Column C to achieve the desired results (refer my
original post) noting that the revision hierarchy is A,B,0,1 (ie A = lowest
and 1 = highest). The data I submitted is only a small sample. Formula
would need to allow for hierarchy as A,B,C,D,.....,Z,0,1,2,3,4,etc.