Extract DAta from text

[gibbylinks]

Hi All

We sell a part that is based on size and has a part number named "CH##R##"
where the number after "CH" is the number of characters and the number after
"R" the number of rows.

These numbers can be 1 or 2 digits long.

I currently use a "vlookup" formula to extract the selling price from a
table, where I manually type in the number of characters and rows.

What I would also like to do is to be able to enter the part number and
extract the information from that.

Could someone show me how to do it ?

I've had a go but because the numbers can be one or two digits I'm having
problems.

Thanks

 

[Dave]

Hi,
Not sure what format you want, but if the part number is in E10,
This formula in a cell will return the digits that follow CH:
=IF(ISNUMBER(MID(E10,3,2)*1),MID(E10,3,2),MID(E10,3,1))
and this next formula in another cell will return the digits that follow R
=IF(ISNUMBER(RIGHT(E10,2)*1),RIGHT(E10,2),RIGHT(E10,1))

Hope this is helpful
Regards - Dave.