General Question

ETpro's avatar

I need advice on a formula to find a specific string in an Excel Spreadsheet and list all cell contents that have that string.

Asked by ETpro (34469points) February 16th, 2010

How can I write a formula to parse a column in Excel and write a list containing each cell that has a specific string as part of its content?

For instance, say Column A is the html name of pages on a web site. I want to generate a new column with every html name that has the string “string” somewhere within it. I’m sure it can be done, but haven’t a clue how.

Observing members: 0 Composing members: 0

6 Answers

lilikoi's avatar

You might be able to use the find function

=Find(thing_to_find,source)

which returns the position of the thing you’re looking for within a string

in conjunction with Excel’s string functions to extract it once you know its position.

This may point you in the right direction. Agree w/ poster below a more specific example would be helpful.

drhat77's avatar

so you want to break up:

page.html
home_string.html
menu.html

to
blank
home_string.html
blank
?

drhat77's avatar

assuming your data starts in A1

=IF(ISERR(FIND(“_string.html”,A1)),””,A1)

ETpro's avatar

@drhat77 Here is exactly what I want to do. I want to loop through a list of html names in column A and find every occurrence of string whether it is in front, the middle or the end of the HTML name, and from that generate a list as in:

SOURDE COLUMN
one.html
two.html
one-string-two.html
one-two.html
string-one-two-html
two-one.html
one-two-string.html

RESULT COLUMN
one-string-two.html
string-one-two-html
one-two-string.html

the search string is constant. What may appear in the other positions is a random set of other strings.

If I end up with a column with some balnks, that’s fine. I can remove empty cells easily in a text editor.

jaytkay's avatar

This gives you the contents of cell A1 if it finds “string”
Otherwise it give empty quotes, a blank (””)

Case-sensitive
=IF(ISERROR(FIND(“string”,A1,1)),””,A1)

Not case-sensitive
=IF(ISERROR(SEARCH(“string”,A1,1)),””,A1)

ETpro's avatar

@jaytkay Excellent. Thanks you so much.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther