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

x
?
Solved

excel clean function removes lowercase d characters

Posted on 2014-02-09
2
Medium Priority
?
216 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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,…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

636 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