Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Excel 2013 formula doesn't seem to be working

Posted on 2015-01-12
Medium Priority
Last Modified: 2016-06-10
Driving me nuts

Please look at attched sheet and tell me why ;

           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
Question by:lifeactuary
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4

Author Comment

ID: 40545535
Wht's wrong??????
LVL 37

Expert Comment

by:Neil Russell
ID: 40545551
Your text probably has a space after it.

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

Author Comment

ID: 40545606
Whew! Finally got it!

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

But why didn't TRIM take care of that?
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.


Author Comment

ID: 40545677
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.


Accepted Solution

Thymos68 earned 2000 total points
ID: 40550462
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

Author Comment

ID: 40551019
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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question