Search For Common Text in Column

[Kcope8302]

I am trying to search for specific words in column A and then print an
abbrviated version of those specific words in Column B. There is a set of 7
words that I want to search for and if one is found i want to print the
abbreviated word if not I want to move to the next word I am searching for.

I have been trying to use the If, Lookup and search functions. I have not
been successful. Example with 2 of the words I am searching for. I want this
to be one string so I can just place in column B.

=IF(SEARCH("Functional",A4),"Fs",IF(SEARCH("Technical",A4),"ts")

 

[Gary''s Student]

Say we have a list of words in column A and the translation table from H1
thru I7

Run this small macro to put the abreviations in column B:

Sub kcope()
i = 1
While Cells(i, "A").Value <> ""
v = Cells(i, "A").Value
For j = 1 To 7
If v = Cells(j, "H").Value Then
Cells(i, "B").Value = Cells(j, "I").Value
Exit For
End If
Next
i = i + 1
Wend
End Sub

 

[Bernie Deitrick]

Put your list of 7 words in F2:F8, and the abbreviations in G2:G8.

Then, in B4, array enter (enter using Ctrl-Shift-Enter)

=INDEX($G:G,MIN(IF(ISERROR(SEARCH($F$2:$F$8,A4)),1000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP

 

[Kcope8302]

Is there a functional statement that would work for my issue. I have 8
reoccuring phrases in cell A. In each of those phrases I want to search for a
specific word and once found just print the abbreviation in cell be next to
it.
Example:

A B
Functional Issues FS
Technical Issues TS
Formal Requirements FREQ
Development DEV
Testing Readiness TEST

I havent been able to find the correct IF/OR statement. Or and other method
that would provide me this functionality.

 

[Gary''s Student]

A beautiful solution.

 

[Kcope8302]

Thank you, That worked for me. I do have one more question. I am having an
issue with excel auto counting when i past an equation. When i past it, the
program automatically starts count up from the origin point. Is there a way
to state in an equation what is allowed to count up and what isnt.

Example:
=AVERAGEIFS(Data!P2ata!P2957,Data!C2ata!C2957,A2,Data!B2ata!B2957,"Req")

The only point that I want to count is the A2. I would like the other
statements to remain as I had them. Is there a method of doing this without
having to past and then go back and alter each cell by itself?

 

[Glenn]

Thank you, That worked for me. I do have one more question. I am having an
issue with excel auto counting when i past an equation. When i past it, the
program automatically starts count up from the origin point. Is there a way
to state in an equation what is allowed to count up and what isnt.

Example:
=AVERAGEIFS(Data!P2ata!P2957,Data!C2ata!C2957,A2,Data!B2ata!B2957,"Req")

The only point that I want to count is the A2. I would like the other
statements to remain as I had them. Is there a method of doing this without
having to past and then go back and alter each cell by itself?

Look at "About cell and range references" in the help file. Specifically, the
part about "The difference between relative and absolute references".

 

[Gord Dibben]

What would be an example of a "specific word" in each of the example phrases
you posted?

Or is each entry in column A the "specific word"?

Maybe a lookup table and an VLOOKUP formula(s)?

See help on VLOOKUP


Gord Dibben MS Excel MVP

 

[Bernie Deitrick]

For example,

Data!P2ata!P2957

Should be:

Data!$P$2:$P$2957

HTH,
Bernie

 

[Harlan Grove]

Put your list of 7 words in F2:F8, and the abbreviations in G2:G8.

Then, in B4, array enter (enter using Ctrl-Shift-Enter)

=INDEX($G:G,MIN(IF(ISERROR(SEARCH($F$2:$F$8,A4)),1000,ROW($F$2:$F$8))))

and copy down to match column A.

....

If one and only one of the 7 words would appear in each cell in col A,
then with the same setup in F2:G8, this could be done using the
formula

=LOOKUP(2,1/SEARCH($F$2:$F$8,A4),$G$2:$G$8)

 

[Kcope8302]

Bernie you have been a great help. Last question. Using the Averageif
Function. How can i average all numbers in my worksheet ignoring the #DIV/0

 

[Bernie Deitrick]

Array enter (enter using Ctrl-Shift-Enter) a formula like

=AVERAGE(IF(NOT(ISERROR(C2:C16)),C2:C16))

HTH,
Bernie
MS Excel MVP