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

x
Solved

# Excel 2013 formula doesn't seem to be working

Posted on 2015-01-12
Medium Priority
31 Views
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
0
Question by:lifeactuary
[X]
###### 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
• 4

Author Comment

ID: 40545535
Wht's wrong??????
0

LVL 37

Expert Comment

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

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

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?
0

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.

Tnanks
0

LVL 1

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
0

Author Comment

ID: 40551019

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

## Featured Post

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.
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.
###### Suggested Courses
Course of the Month9 days, 1 hour left to enroll