Search For Common Text in Column
 

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("Techni cal",A4),"ts")

Search For Common Text in Column
 

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

--
Gary''s Student - gsnu200818


"Kcope8302" wrote:

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("Techni cal",A4),"ts")

Search For Common Text in Column
 

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)),1 000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
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("Techni cal",A4),"ts")

Search For Common Text in Column
 

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" wrote:

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

--
Gary''s Student - gsnu200818


"Kcope8302" wrote:

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("Techni cal",A4),"ts")

Search For Common Text in Column
 

A beautiful solution.
--
Gary''s Student - gsnu200818


"Bernie Deitrick" wrote:

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)),1 000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
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("Techni cal",A4),"ts")

Search For Common Text in Column
 

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!P2:Data!P2957,Data!C2:Data!C2957, A2,Data!B2:Data!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?

"Bernie Deitrick" wrote:

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)),1 000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
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("Techni cal",A4),"ts")

Search For Common Text in Column
 

Kcope8302 wrote:
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!P2:Data!P2957,Data!C2:Data!C2957, A2,Data!B2:Data!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".

Search For Common Text in Column
 

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


On Mon, 8 Dec 2008 11:09:01 -0800, Kcope8302
wrote:

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" wrote:

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

--
Gary''s Student - gsnu200818


"Kcope8302" wrote:

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("Techni cal",A4),"ts")

Search For Common Text in Column
 

For example,

Data!P2:Data!P2957

Should be:

Data!$P$2:$P$2957

HTH,
Bernie

"Kcope8302" wrote in message
...
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!P2:Data!P2957,Data!C2:Data!C2957, A2,Data!B2:Data!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?

"Bernie Deitrick" wrote:

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)),1 000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
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("Techni cal",A4),"ts")

Search For Common Text in Column
 

"Bernie Deitrick" <deitbe @ consumer dot org wrote...
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)

Search For Common Text in Column
 

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" wrote:

For example,

Data!P2:Data!P2957

Should be:

Data!$P$2:$P$2957

HTH,
Bernie

"Kcope8302" wrote in message
...
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!P2:Data!P2957,Data!C2:Data!C2957, A2,Data!B2:Data!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?

"Bernie Deitrick" wrote:

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)),1 000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
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("Techni cal",A4),"ts")

Search For Common Text in Column
 

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

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

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
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" wrote:

For example,

Data!P2:Data!P2957

Should be:

Data!$P$2:$P$2957

HTH,
Bernie

"Kcope8302" wrote in message
...
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!P2:Data!P2957,Data!C2:Data!C2957, A2,Data!B2:Data!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?

"Bernie Deitrick" wrote:

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)),1 000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
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("Techni cal",A4),"ts")