Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Cimorene
 
Posts: n/a
Default Filter long Text strings

The spreadsheet is thousands of rows and each column has large text strings
that must be broken down into individual columns. The spreadsheet contains
data about retired racing greyhounds, their owners, and all of their
littermates.

I've been using the text to column function for some relatively structured
data columns and, in the more unstructured columns, the tip on
www.contextures.com for filtering a long text string to find the values of:
male
female
adopt
racing

There are columns for thirteen littermates that all have adjoining Sex and
Status fields. They need to be filtered.

The data for littermate4 looks like this:

AC AD AE AF
Littermate4 female L4Sex L4Status
Rapido Wonderful female
Palmer male

AC is the column that needs to be filtered. When tested for True all females
will display in AD as the value True. Then I can paste the word female into
column AE. Then I pick all to display and change the label from female to
male and when filtered as True the word male can be pasted into AE. Same
procedure for adopt except that gets pasted into AF. This worked using this
formula through column Z.

Things have come to a complete stop because the function no longer works. :(

The function below should return a value of true or false, but returns a
zero and an error message

=ISNUMBER(SEARCH($AC$1,AD2))

The Excel error message states:

Microsoft Excel cannot calculate a formula. Cell references in the formula
refer to the formulas result, creating a circular reference. Try one of the
following:

€¢ If you accidentally created the circular reference, click OK. This will
display the Circular Reference toolbar and help for using it to correct your
formula.
€¢ For more information about circular references and how to work with them,
click Help.
€¢ To continue, leaving the formula as it is, click Cancel.

Couldnt figure out what to do with that little circular reference toolbar
after clicking okay.

Tried the second suggestion and changed the value to 5000. Result changed
from zero to False. Tried to go to a 50,000 number and the number wasnt
acceptable. Went to option three.

The third option doesnt change anything and gets me no closer to getting
the data sorted into new columns.

Gave up and came here. :(

TIA for any and all help!!!

--
From the ashes arose the Phoenix, raising wide its arms to embrace new life!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find nth instance of a character in a string Francis Hayes (The Excel Addict) Excel Discussion (Misc queries) 7 January 21st 05 04:44 PM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 01:37 AM
Macro or Function to make text size to suite text Length? lbbss Excel Discussion (Misc queries) 4 December 14th 04 08:53 PM
advanced filter won't allow me to filter on bracketed text (-456.2 LucianoG Excel Discussion (Misc queries) 1 December 6th 04 09:38 PM
Long Text Entry jaspikes Excel Discussion (Misc queries) 1 December 3rd 04 11:38 PM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"