• 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.

Substitute function on non-printing characters is not working

D

DashboardNovice

Member
In the attached file, I have some data copied from a website. Evidently, some non-printing characters came with the data.

I am using the SUBSTITUTE function to find the non-printing character (which has a Code of 160) and replace it with a blank (columns D:F).

Then I copy/paste values into columns I:K. When I select these, in the status bar at the bottom, Excel returns a count instead of a sum of these numbers. I have applied 'Number' format to these columns and it is still not working.

I have used this formula successfully many times before, but for some reason it is not working now.

What am I doing wrong?
 

Attachments

How do you know it's text? The format says 'Number.'
Click to expand...
Just because the the format says it is a number does not mean it is so, suggest you pull your chin in learn and some manners, I did not use a function to see that the data block was text but perhaps you should use =ISTEXT and find out for yourself
 
bobhc said:
suggest you pull your chin in learn and some manners
Click to expand...

What are you talking about? What did I say or do that was rude?

bobhc said:
I did not use a function to see that the data block was text but perhaps you should use =ISTEXT and find out for yourself
Click to expand...

1. You sound as though I accused you of using a function, which I did not do.

2. "Find out for yourself." Why such a harsh tone? You act as if I haven't even tried to figure this out on my own. Moreover, if people did "find out for themselves" then there wouldn't be any need for this website.

In short, I think you are reading into things way to much. I still can't believe how you have replied. Unbelievable.
 
@DashboardNovice

When ever you do arithmetic operation on numbers which are stored as TEXT, they get converted to numbers, so +0 or -- (double negation) are actually arithmetic operation without affecting the numbers.

Regards,
 
I point to your post where you cast doubt on me

How do you know it's text? The format says 'Number.'
Click to expand...

I know because as my previous post #6 explained

As I have have previously said,...pull your chin in.


.
 
You must log in or register to reply here.
Back
Top