Find a specific value in a cell (numerics)

[birdie27]

I am trying to check some data (about 5000 rows worth) and don't want to have to go through each row. What I need to do is check whether the 7th number in a string is below 5, and then to return a text result for above 5 and a different result for below 5. For example:

B4: 88050170088
7th value = 7, therefore return "Male"

I have tried =IF(MID(B4,7,1)>5,"Female","Male") but it is not working and I can't figure out why..could also just be making a rookie mistake....

[sktneer]

This is because MID(B4,7,1) returns a number as text and you are comparing it with a pure number.
So try either of these...

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Any mathematical operation on a number stored as text converts it back to a number.

[Fotis1991]

What do you mean saying "is not working"?

Your formula in your example gives a "Female" as result which is correct when B4 is a number.

[birdie27]

Thanks very much - makes sense now

[birdie27]

The formula was only returning one result - but I have come right with the above formula suggestions because I wasn't taking all factors into account

Thanks for the kind assistance all

[sktneer]

You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

[vikaskamble87]

Just replace the words Male and female..
Because the condition is that if the value at 7th Position is greater than 7, then it should result as male. try this;

=IF(MID(B4,7,1)>5,"Male","Female")


Regards,
Vikaskamble87