asked on

Excel 2013 formula doesn't seem to be working

Driving me nuts

Please look at attched sheet and tell me why ;
eg

A B C
1 Bag male female =trim(clean(a1) =Right(b1,6) C1 shows------ emale (no "f")
8 Bow tie male =trim(clean(a8) =Right(b8,6) C8 shows ------ male (1st is blank)TestMF.xlsx

lifeactuary

ASKER

Wht's wrong??????

Neil Russell

Your text probably has a space after it.

Clean ONLY removes non printable chars. Space is printable. Use trim instead.

lifeactuary

ASKER

Whew! Finally got it!

For some reason the spaces are showing as char(160).

But why didn't TRIM take care of that?

lifeactuary

ASKER

But I used Trim(Char...)

Could you make the correction and send it to me?

I tried using SUBSTITUTE(CHAR(32)," ") and SUBSTITUTE(CHAR(160)," ") but still having a problem.

Tnanks

ASKER CERTIFIED SOLUTION

Thymos68

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lifeactuary

ASKER

Thanks for the reply.

I found a solution by copying the column containing the problem field into a new worksheet, doing a Text-to-Column, and then combing the result into a single cell with a space between each word,

Wonder why CLEAN doesn't remove CHAR(160)?

Thanks again.
Ed