Remove spaces in pasted numbers

I have pasted a large range of values - slightly inconvenient these values contain spaces, like this:

104 232
97 122
89 396
87 247

Since its numbers the substitute function doesn't work. Any suggestions how to crack this?

Thanks.

Since its numbers the substitute function doesn't work.

Sure it does:

Formula:

Please Login or Register  to view this content.

Regards, TMS

I note that you have been active on the forum since this solution was posted. Please let me know if it resolves your issue.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

BTW, I would also think find and replace would work, it did when I tried it.

@Sambo kid: absolutely, no reason why not ... and leaves real numbers. I was just addressing the assertion that "the substitute function doesn't work."


Regards, TMS

Sure, got it. I'm sure it didn't slip your mind, just thought I'd mention it to the OP. BTW, I wondered why you had the two dashes in front of the formula, I had success w/substitute w/o them, then I tried it with them and saw it formatted as # instead of text. Cool.
That's why I like this forum, I always learn stuff... thx.

@Sambo kid: yes, use a double negative to coerce a text number into a real number. Or, you can multiply by 1, or you can add 0

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Not a lot in it, more a matter of personal taste. Note that they ALL fail if the cell is empty or contains spaces. SO you'd need an IFERROR to cater for that.


Regards, TMS

@Sambo kid: thanks for the feedback and rep.

you referring to my recommendation about find "space" and replace all "" or TMS's rec?

It might help if you provided a sample workbook showing what you are working with?

Try this

=--SUBSTITUTE(A2,CHAR(160),"")

Originally Posted by AlKey

Try this

=--SUBSTITUTE(A2,CHAR(160),"")

Ahaa! Success. That worked. Thanks

Would you mind elaborating on the CHAR(160) part of the formula? Just so I can understand how it works.

I think Find/Replace should works

Ctrl-H
Find what":<hit space bar>
Replace with: <leave it blank>

Originally Posted by Jonathan9

Ahaa! Success. That worked. Thanks

Would you mind elaborating on the CHAR(160) part of the formula? Just so I can understand how it works.

160 is a NON-BREAKING HTML SPACE character. It can't be removed by trim or substitute regular space.


Thanks for the feedback!