Solved

excel clean function removes lowercase d characters

Posted on 2014-02-09
2
207 Views
Last Modified: 2014-02-09
This is driving me nuts. I found a formula to clean special characters from excel text that get carried along with some csv files I download. It "used to" work but now it's broken. this is the formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CLEAN(A57)),"$",""),"%",""),CHAR(160)," "),CHAR(100)," "),CHAR(152)," ")

However it deletes all lowercase "d" characters. Is there a better way to do this or is there an error in the formula?
0
Comment
Question by:orerockon
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39845599
Your formula is replacing CHAR(100) with a space, CHAR(100)="d" - that should probably be CHAR(10) which is a "line break" character in Excel.

If you simply change CHAR(100) to CHAR(10), though, you won't get CHAR(10) replaced by a space because CLEAN function will remove CHAR(10) before you get to the SUBSTITUTE function, so you need to change the order of the functions a little like this:

=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( TRIM( CLEAN( SUBSTITUTE(A57, CHAR(10)," "))),"$",""),"%",""),CHAR(160)," "),CHAR(152)," ")

regards, barry
0
 

Author Comment

by:orerockon
ID: 39845687
Looks like it works, thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now