I need a formula that will examine a cell for a certain value, then return a
specified portion of that value to another cell. For instance, B1 = RR 4 Box
154. B2 = PO Box 555. I need a formula in the corresponding A column (A1 for
instance) that, if the cell begins with "RR", it will return that value
(along with the number beside it - 4 in the example above) to A1. If it's
doesn't begin with "RR" then I don't need a return. Any help greatly
appreciated. You guys and gals are the masters!

Will the number after the RR always be a single digit? If so, you could use:

=IF(LEFT(B1,2)="RR",MID(B1,4,1),"")

If the number will be of variable length, then try:

=IF(LEFT(B1,2)="RR",MID(B1,4,FIND("~",SUBSTITUTE(B 1," ","~",2))-4,"")

HTH
Elkar


"mailrail" wrote:

I need a formula that will examine a cell for a certain value, then return a
specified portion of that value to another cell. For instance, B1 = RR 4 Box
154. B2 = PO Box 555. I need a formula in the corresponding A column (A1 for
instance) that, if the cell begins with "RR", it will return that value
(along with the number beside it - 4 in the example above) to A1. If it's
doesn't begin with "RR" then I don't need a return. Any help greatly
appreciated. You guys and gals are the masters!

It isn't clear what you are after. Suppose B1 has

RR 4 Box 154

Do you want to return the entire contents of B1? Or do you want only
the RR and the following number ("RR 4")?

For the whole value, use
=IF(LEFT(B1,2)="RR",B1,"")

For just the RR and the number, use
=IF(LEFT(B1,2)="RR",LEFT(B1,FIND(" ",B1,FIND(" ",B1,1)+1)),"")

In both formulas, if B1 does not begin with "RR" (upper or lower case,
doesn't matter), nothing is returned.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 19 Mar 2009 13:06:02 -0700, mailrail
wrote:

I need a formula that will examine a cell for a certain value, then return a
specified portion of that value to another cell. For instance, B1 = RR 4 Box
154. B2 = PO Box 555. I need a formula in the corresponding A column (A1 for
instance) that, if the cell begins with "RR", it will return that value
(along with the number beside it - 4 in the example above) to A1. If it's
doesn't begin with "RR" then I don't need a return. Any help greatly
appreciated. You guys and gals are the masters!