extracting specific values and replacing in set column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,
i am trying to extract a value from a column that contains multiple
'selections' separated by a ';" sign. Ie in Column A i want to find every
value 'general' and input that value in column B the same row. Any formulas?
thanks so much!
Hudini
 
Try this in B1, and copy down as needed:

=IF(ISNUMBER(SEARCH("general",A1)),"general","")
 
Thanks RD that was very helpful! any ideas if i have to do this over 30
times? ie there are 30 values in row A that i want to extract and input
correctly in Row B.
Thanks again!
Hudini
 
By "Copy down as needed", I meant ... with values in A1 to A30,
After entering the formula in B1,
Click back in B1 to select it.

In the lower right corner of the selected B1, you'll see a tiny black
square.
Hover your cursor over this square until the cursor changes from a fat white
cross to a skinny black cross -
THEN - click and drag down to B30.
This will cause the formula to copy itself down Column B, changing the
relative cell references in the formula, dependant on the row it's copied
to.

Another copy approach (*if* A1 to A30 contain *no* empty cells),
is to *double* click on that tiny black square in B1,
which will automatically copy the formula in B1 as far down Column B as
there is data in Column A.
 
Actually, what i meant is that i have over 30 values in column a that i want
to replace in column b if indeed it is in column a. IE, the formula might
look like this =IF(ISNUMBER(SEARCH("general","general2", "general3",
A1)),"general","") is that possible and if so how exactly should the formula
look?
thank you!!
Hudini
 
I don't quite follow exactly what you're trying to do.

Are you saying that you have 30 possible values to search for in Column A,
and you want to be able to *change* the value you're looking for at any
particular time?

If so, enter the value to find in say C1, and try this:

=IF(ISNUMBER(SEARCH($C$1,A1)),$C$1,"")

To find a different value, just change the contents of C1.

OR, are you saying that you're looking for *any* of those 30 values at the
*same time*, and want to flag the row that they're found in.

To reference those 30 values you'll need to make a datalist, say in an
out-of-the-way location, Z1 to Z30.
Since these particular values will be combined with other values within the
cells, this datalist *must* include the asterisk wildcard,
something like this:

*Tom*
*Dick*
*harry*
*general*
.... etc.

Now, assign a name to this data range.

Select Z1 to Z30, and click in the name box (left of the formula bar), and
type something short, like:
list
Then hit <Enter>.

Now, in B1, enter this formula:

=IF(SUMPRODUCT(COUNTIF(A1,INDIRECT("list")))>0,"FOUND","")

And copy down as needed.

Each time one or more of the values in your datalist is found in Column A,
"FOUND" will display in the corresponding row in Column B.

If neither of these guesses of mine are what you want, post back with a more
definitive explanation.
 
Back
Top