Multiple lookups returning value after string

[maddog9486]

I am currently, just using the RIGHT formula to return the last 4 characters from the cell, but where i run into problems is when the number at the end is double digits, it cuts off the text portion. So i found some other formulas, but they don't quite work yet so I thought of trying to combine these. I thought i could search for the text items, then once found return anything after that. The two new formulas are as follows:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Maybe there is an easier way. Basically, what should happen is the following:

TEXT	EXPECTED RESULT	CURRENT RESULT	LOOKUP DATA
MCLBINDVARR1	AAR1	AAR1	ITR
MCLBINDVMaj1	Maj1	Maj1	MAJ
MCLBINDVMaj3	Maj3	Maj3	PMR
MCLBINDVMaj14	Maj14	aj14
MCLBINDVPMR1	PMR1
[blank cell]

[hoyasaxa215]

Assuming the text starts in A1, use the formula below and copy down:

Please Login or Register  to view this content.

[maddog9486]

Assuming the text starts in A1, use the formula below and copy down:

Please Login or Register  to view this content.

Sorry, forgot to mention that those first 8 characters in that string could vary. So "MCLBINDV" may be something else. My example was just all set to the same.

[hoyasaxa215]

So you just want whatever is after the first 8 characters? Does this work for you?

Please Login or Register  to view this content.

[maddog9486]

Wow, that was simple. Forgot all about MID. Thanks Hoya.

[maddog9486]

Ok, so I have discovered that in the original text column, the first few characters may be 7 or 8 before the required text is needed. For example:

TEXT	EXPECTED RESULT	CURRENT RESULT	LOOKUP DATA
MCLBINDVARR1	AAR1	AAR1	ITR
MCLBINDVMaj1	Maj1	Maj1	MAJ
MCLBINDVMaj3	Maj3	Maj3	PMR
MCLBINDVMaj14	Maj14	aj14
MCLBINDVPMR1	PMR1
MLCCSBAPMR10	PMR10	MR10

So you can see that the MID formula works fine when the starting characters (project code) is 8 digits long, but if the project code is only 7 characters, it pulls incorrectly. I think this might be why I was trying to use FIND or SEARCH originally.