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
lifeactuaryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Thymos68Connect With a Mentor Director of OperationsCommented:
Char(160) is a non-breaking space, so it's treated as a character, and not something TRIM or CLEAN will remove.

Try including a substitution of the non-breaking space (ascii 0160), with a regular space (ascii 0032)

in B1, enter the following  =CLEAN(TRIM(SUBSTITUTE(A1," "," ")))
but be sure to key-in the ascii char 160 by pressing alt-0160 for the first space, then a normal space for the second

Good luck
0
 
lifeactuaryAuthor Commented:
Wht's wrong??????
0
 
Neil RussellTechnical Development LeadCommented:
Your text probably has a space after it.

Clean ONLY removes non printable chars.  Space is printable. Use trim instead.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Access It Instantly
 
lifeactuaryAuthor Commented:
Whew! Finally got it!

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

But why didn't TRIM take care of that?
0
 
lifeactuaryAuthor Commented:
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
0
 
lifeactuaryAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.