Source
r/excel•
Farrix

Finding and comparing length after locating a special character

I'm trying to find a hyphen in a word and compare the length of the word left after the hyphen.

For example:
Mary-Bell has a hyphen in it. I want to take everything after the hyphen (length of four) and compare it to be greater than or equal to seven in length. Bell is less than seven in this case so it would ideally trigger the "length < 7" function.

My code:
(to find if the last half of the name is > 6)
IF(AND(ISERROR(FIND("-",B5,1))=FALSE, LEN(MID(B5,FIND("-",B5)+1,99)>6),

(to find if the last half of the name is < 7)
IF(AND(ISERROR(FIND("-",B5,1))=FALSE,LEN(MID(B5,FIND("-",B5)+1,99)<7),

Unfortunately, right now this is giving me an error. How do I find the length of the remainder of a word after using MID to get the position to start in the middle?

**Edit: Got my initial issue sorted.

Posted Sep 10, 2012, 10:23 AM UTC · edited

24
cobainbc151•

I believe the problem is with your parenthesis at the end of the LEN function. It should look like this instead:

LEN(MID(B3,FIND("-",B3)+1,99))<7

LEN(MID(B3,FIND("-",B3)+1,99))>6

With an additional closed parenthesis before the < or > signs.

Those came up as TRUE and FALSE when I put them for Mary-Bell.

Hope that solves it!

2
FarrixOP•

=LOWER(

IF(AND(ISERROR(FIND("-",B5,1))=TRUE,LEN(B5)>6),

LEFT(B5,99),

IF(AND(ISERROR(FIND("-",B5,1))=TRUE,LEN(B5)<7),

CONCATENATE(B5,LEFT($E$3,7-LEN(B5))),

IF(AND(ISERROR(FIND("-",B5,1))=FALSE,LEN(MID(B5,FIND("-",B5)+1,99))>6,

MID(B5,FIND("-",B5)+1,99),

IF(AND(ISERROR(FIND("-",B5,1))=FALSE,LEN(MID(B5,FIND("-",B5)+1,99))<7,

CONCATENATE(MID(B5,FIND("-",B5)+1,99),LEFT($E$3,7-LEN(B5))), 1)))))

This is the entirety of my code. I tried making the changes you suggest but it's still error-ing out. The formatting is just for readability.

**Edit: I figured out part of the issue.

***Edit: Figured out the formatting. Now I'm just having a problem with the very last line in the code. Thanks for the help!

1
cqxray7•

=IF(LEN(MID(B3,FIND("-",B3)+1,99))<7,"Part after hyphen has fewer than 7 characters","Part after hyphen has 7 or more characters")

You only have to test once, whether the part after the hyphen has fewer than 7 characters. You will get either TRUE (that part has 0 to 6 characters) or FALSE (that pat has 7 or more). If you need to have an ERROR check, put that on the outside of everything:

=IFERROR(IF(LEN(MID(B3,FIND("-",B3)+1,99))<7,"Part after hyphen has fewer than 7 characters","Part after hyphen has 7 or more characters"),"n/a")

2
FarrixOP•

This was my first time using a lot of those functions and I was really having to go off of what I could find online about how they work. As a result, I ended up coding everything as implicitly as I could, just so I could read back over it more easily to see what I was doing where.

Thanks for the info though! It is appreciated. :)

1