Solved

excel clean function removes lowercase d characters

Posted on 2014-02-09
2
213 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
[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
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

717 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