Weight colour age columns

[Sussexguy44]

Hi,

I looking to create a formula to find horses that are male and are carrying less tham 10. 4ibs. I have the two colums in place. The colour age column shows B/G and B/F. I only need the G and F element of this. In the weight column the data shows 4/10-5. I only need the weight element of this not the age. Is there a formula which can find only the parts I need and then put a 1,0 in a colums if yes or no?

[Sussexguy44]

To clarify Im looking to select male horses carrying 10st 4 Ilbs or less.

[Captain Sensible]

Personally I think you'd be better off writing a custom function in VBA rather than formulas as formulas will get very messy.

With formulas you'd use search and find formulas to split the text. Maybe check out https://www.ablebits.com/office-addins- ... ing-excel/

[GaryRussell]

You could also try the textsplit function. It outputs the contents into two cells.

eg. where y5 is the cell containing age/weight

Code: Select all

=TEXTSPLIT(y5,"/")

[Sussexguy44]

Hiya,

=IF(ISNUMBER(SEARCH("G",Z5)),1,0). I have pursued this and the formula as noted works for finding the sex of the horse. It finds the G (Gelding) and give me a 1. This would only work for NH racing as in Flat racing there are colts which would have a C. I will pursue the TEXTSPLIT function for the weight being carried.

[Captain Sensible]

Never heard of textsplit before, learn something new everyday.

You didn't mention you were looking for a C in the opening post so didn't include it. If it's only C and G and they wouldn't appear in some other form in the cell you could use countif

=COUNTIF(A1;"*C*") + COUNTIF(A1;"*G*")

[Sussexguy44]

Thanks Captain, this is better than I currently have. To make it 100% I would like to exclude all data to the left of the /. So the data I am looking at is the colour and age on the horse. This is shown in the cell as b/g which is a bay gelding. Could I use the countif function and only search data to the right of the / as I am only interested in the sex of the horse.

[Captain Sensible]

Yep easy enough to split the string by the / just by using the RIGHT function

=RIGHT(A1, SEARCH("/",A1,1)-1)

Also the TEXTSPLIT option Gary suggested by that doesn't work with my version of excel as I think TEXTSPLIT is new.

But the best thing to do is always to go with the easy option which if the data is ouput in a regular format why not just search for /G or /C like

=COUNTIF(A1,"*/C*") + COUNTIF(A1,"*/G*")

Otherwise you start making formulas unneccessarily long