Excel:Extracting part of the data from a cell

December 2, 2011 at 05:47:24
Specs: Windows XP
Hey there,

Ive got a list which look like these in column A

yorkshire-outdoors.co.uk Selling Stakes Cl6 1m7f177y
bet365.com FilliesĀ“ Handicap Cl5 1m5f22y
UC Better Maiden Stakes Cl5 6f


What I want to do is to extract to cell B the data which is in bold. (b1 would be "Selling stakes" etc). Im trying with the function
=MID(A1,FIND("Selling Stakes",A1),Selling Stakes)
but im getting nowhere. Is there any way to extract it?
Thanks for looking
Pablo


See More: Excel:Extracting part of the data from a cell

Report •


#1
December 2, 2011 at 07:22:05
In order to use a single formula to extract an ever changing text string from numerous cells, you need some sort of consistency within the text string.

Some examples:
- Return the string starting in the 5th position, ending after 9 characters
- Return the string starting after the 3rd space, ending before the 5th space
- Return the string starting after the 1st comma, ending after 2 spaces
- etc.

With no consistency as far as where the string starts or ends, you can't build a "one formula and drag it down" solution.

The only consistency I see in your examples is that all strings end before the space that is just before an uppercase C. That's not enough for Excel to work with because there appears to be no consistency as to where the desired string starts. In addition, some cells contain another uppercase C so that is going to make it more complicated also.

It might be possible to do it with VBA but you would need 1 of 2 things to exist:

1 - A list of strings to search for, which if you had, would probably mean that you don't need any help. ;-)

2 - If the strings are really bold as in your example, then VBA could search each cell character by character and extract the bold characters, one by one, building the extracting string one cahracter at a time.

I'm assuming that you bolded the strings only for emphasis in your post.

One other option is to use Data...Text-To-Columns delimited by a space to create a bunch of columns with one string in each cell and then go back and clean things up manually.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 2, 2011 at 07:40:39
i've found a solution for this. It works with the function:

=
IF(ISNUMBER(SEARCH("selling stakes",A1)),"selling stake",
IF(ISNUMBER(SEARCH("maiden stakes",A1)),"maiden stakes",
IF(ISNUMBER(SEARCH("Handicap",A1)),"Handicap")))


Report •

#3
December 2, 2011 at 08:23:11
I must be missing something here.

Are those the only three strings you are searching for?

I assumed you had many different entries in Column A with many different strings to extract.

If all you have are those 3 strings to search for and extract, then this will work also:

=IF(ISERROR(FIND("Selling",A1)),
IF(ISERROR(FIND("Maiden",A1)),
"Handicap","Maiden Stakes"),"Selling Stakes")

A formula tip:

When you are using an IF function to check X number of criteria, and you know that at least one of the criteria will always be TRUE, you only need X-1 IF's.

For example, in your situation, you have 3 criteria: the 3 strings. If you know that at least one of them will always be found, then you only need to check for 2. If those 2 don't exist, then the 3rd one must.

You'll note that I don't search for Handicap since if Selling and Maiden don't exist, the answer must be Handicap.

In addition, I don't bother searching for Stakes since it's common to both the Selling and Maiden strings.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 2, 2011 at 08:39:13
There is more strings but I assume it would be easy to add them later on. It works OK at the moment although Im experiecing another problem. My A1 column looks like that:
yorkshire-outdoors.co.uk Selling Stakes Cl6 1m7f177y
bet365.com FilliesĀ“ Handicap Cl5 1m5f22y
UC Better Maiden Stakes Cl5 6f
Selex Galileo Novices Handicap Chase Cl4 2m

The function is

=IF(ISNUMBER(SEARCH("selling stakes",A1)),"selling stake",
IF(ISNUMBER(SEARCH("handicap",A1)),"handicap",
IF(ISNUMBER(SEARCH("maiden stakes",A1)),"maiden stakes",
IF(ISNUMBER(SEARCH("Novices Handicap Chase",A1)),"Novices Handicap Chase"))))

A4 cell returns "handicap" instead of "Novices Handicap Chase". I know i can swap the positions (search for Novice Handicap Chase first, than for Handicap). But is there a way around it for example just search for exact matches?

Thanks for your input DerbyDad


Report •

#5
December 2, 2011 at 08:59:52
re: "But is there a way around it for example just search for exact matches?"

Putting the Novice search first is the best way since the formula will "stop" after the first TRUE is returned.

Once again, you can shorten your formula by only searching for Novices to return Novices Handicap Chase, assuming Novices is unique to that string.

re: "There is more strings but I assume it would be easy to add them later on."

BTW...unless you using Excel 2007 or later, you are going to run out of available IF clauses. Excel 2003 and earlier only allow 7 IF's in nested IF.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question