• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to combine two Excel functions (TRIM and CLEAN) in one formula?

Eloise T

Eloise T

Active Member
This should be intuitively obvious but I'm not figuring it out.

I want to combine two Excel functions (TRIM and CLEAN) into one formula, if possible:

=TRIM(A2) and =CLEAN(A2) so that both superfluous spaces (ASCII 32) and ASCII characters 0-31 are removed.

=TRIM(A2)&CLEAN(A2) ← This does not work as it only removes the spaces and undesirably duplicates the data.

Please see attached sample data.
 

Attachments

  • TEST FILE.xlsx
    10.6 KB · Views: 7
DUH! :confused: THANK YOU!



TRIM works, but CLEAN does not. Any suggestions?
 

Attachments

  • TEST FILE 2.xlsx
    10.6 KB · Views: 16

It seems you misread what CLEAN Excel function does …

As I think there is no function for your need, you have to move to VBA …
​
 
To elaborate on what Marc said, CLEAN is meant to remove non-printable characters, such as other space type characters. The symbols in your workbook, while not alphanumeric, are still printable.

Are you wanting a function/formula that only keeps alphanumerics?
 
Luke M said:
To elaborate on what Marc said, CLEAN is meant to remove non-printable characters, such as other space type characters. The symbols in your workbook, while not alphanumeric, are still printable.

Are you wanting a function/formula that only keeps alpha-numerics?
Click to expand...

I need the formula to TRIM off the spaces and CLEAN hidden/invisible non-printable characters. I get them frequently when salesmen send me their weekly sales numbers. Particularly the ASCII 9 (TAB) character. CLEAN works very well but I often have to deal with extra, unwanted, superfluous spaces as well so I was hoping to kill two problems with one formula.

This brings me to trying to create a sample test file for my own testing and for getting help here. When trying to create the sample/test file, I could create the ASCII characters using ALT-Numpad, but could not make them invisible, hence your point about them needing to be non-printable and to my dismay, not being CLEANed off since they were visible.

=CLEAN(TRIM(cell location)) should work, correct?
Any idea how to create in hidden/invisible non-printable characters for testing?
 
Marc L said:
It seems you misread what CLEAN Excel function does …

As I think there is no function for your need, you have to move to VBA …
​
Click to expand...
I know what CLEAN does but couldn't seem to replicate the nasty non-printable characters for testing. Suggestions for creating a test file with non-printable characters?
 
Hi Eloise ,

Are the characters you have shown in your uploaded file , the only ones you wish to remove ?

CLEAN will not remove characters such the space-like character with the ASCII code of 160 ; thus , given the variety of unwanted characters , the best way would be to have a standard data cleaning routine which you can run on your data , which will take care of all unwanted characters.

This thread seems comprehensive , and should help :

http://www.vbaexpress.com/forum/showthread.php?47255-A-Data-Cleaning-Macro-Clean-Trim-and-CHAR(160)

Narayan
 
If your file doesn't actually have the strange symbols, and instead the point it to create some invisible spaces, then yes, overall formula would be like
=TRIM(CLEAN(A2))

To create some test data, it might be helpful to use the CODE and CHAR functions. CODE will tell you the code number for a symbol. So, let's say that with your real data, you want to know the code of the 5th character in a cell. You could do:

=CODE(MID(A2,5,1))

Let's continue and say that the result of the above was, I dunno, 150. To re-create that character elsewhere, you can do:
=CHAR(150)

As Narayan mentioned, 160 is a special case (very annoying too as it pops up commonly). For those, I usually write a formula like this:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),"")))

That will remove the 160 character, other spaces, and then trim the whole thing.
 
NARAYANK991 said:
Hi Eloise ,

Are the characters you have shown in your uploaded file , the only ones you wish to remove ?

I need to remove extra spaces and TRIM works very well.
I also seem to get a lot of ASCII 9 (TAB) characters in the incoming data and CLEAN works for that.
I was trying to combine the two into one formula: =CLEAN(TRIM(cell)) which you reminded how to do.
The problem was trying to create a sample/test file of non-printable characters (ASCII 0-31) to make sure =CLEAN(TRIM(cell)) was doing its job. Maybe I'm being too careful, but I like to cover my backside in case something goes awry. Every time I ALT + (0-31) it does not make a non-printable/hidden/invisible character but one that IS visible. Any suggestions on how to create such a thing?


CLEAN will not remove characters such the space-like character with the ASCII code of 160 ; thus , given the variety of unwanted characters , the best way would be to have a standard data cleaning routine which you can run on your data , which will take care of all unwanted characters.

This thread seems comprehensive , and should help :

http://www.vbaexpress.com/forum/showthread.php?47255-A-Data-Cleaning-Macro-Clean-Trim-and-CHAR(160)

Narayan
Click to expand...
 
I tried the following but I'm getting the #VALUE! error.
=CHAR(0)&CHAR(1)&CHAR(2)&CHAR(3)&CHAR(4)&CHAR(5)&CHAR(6)&CHAR(7)&CHAR(8)&CHAR(9)&CHAR(10)&CHAR(11)&CHAR(12)&CHAR(13)&CHAR(14)&CHAR(15)&CHAR(16)&CHAR(17)&CHAR(18)&CHAR(19)&CHAR(20)&CHAR(21)&CHAR(22)&CHAR(23)&CHAR(24)&CHAR(25)&CHAR(26)&CHAR(27)&CHAR(28)&CHAR(29)&CHAR(30)&CHAR(31)

For some reason it doesn't like CHAR(0) but the rest appear to be ok once CHAR(0) is removed (the #VALUE! error goes away.)
 
Last edited:
Hi,

Please see below idea

TRIM(CLEAN(SUBSTITUTE(LEFT(TRIM(E13),LEN(TRIM(E13))-OR(RIGHT(TRIM(E13))={"?","!","."})),CHAR(160)," ")))
 
You must log in or register to reply here.
Back
Top