EXCEL FEED: RE: [ExcelVBA] Re: Find Replace of spaces

Rabu, 31 Mei 2017

RE: [ExcelVBA] Re: Find Replace of spaces

Hi Paul et.al.

Of course this is a VBA group, not an Excel function group. My VBA code to process the data works well, just the troublesome Hard Space characters that I have never encountered before. I have not found a better place on the internet to provide and ask for help than this group. Yes, maybe I haven't looked hard enough. ;)

=CLEAN() also does not work. The Hard Space character is stubborn.

Hutch's suggestion of CHAR(160) in the SUBSTITUTE function works.

Thank you all (David, Hutch, Paul S, Rajiv, Hafizulla and Yah) for your contributions.

Paul V

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Wednesday, 31 May 2017 8:52 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Re: Find Replace of spaces

Late to the party,

and for some reason, my view of the thread in the Group doesn't show the original discussion.

but I'm "somewhat" confused.

Are we discussing Excel functions or VBA?

with Excel functions, Trim(C1) will remove the spaces at the beginning and end of the value in C1.

But to remove the spaces WITHIN C1, you'd have to use

=SUBSTITUTE(C1," ","")

But that SHOULD also remove the spaces at the beginning and end.

Since it does not,

I suspect that the characters aren't spaces, but ASCII characters that do not have a "display" in the font set.

That is, they're non-printing characters.

You could try: =Clean(C1)

It's supposed to remove the non-printing characters.

Can you copy the worksheet and delete most of the data so it includes a sample cell?

I can run a vba macro to determine the characters present.

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

On Wednesday, May 31, 2017 3:18 AM, "Paul Vermeulen paul.vermeulen@vulcantech.com.au [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

Hi Hutch. No, Trim does not remove the hard spaces. Good try, though, didn't even know there was a TRIM function.

Paul

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Tuesday, 30 May 2017 8:40 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Find Replace of spaces

Hi Paul,

Does =Value (Trim (C1)) return a usable number?

Hutch

Sent from Yahoo Mail on Android

__._,_.___

Posted by: Paul Vermeulen <paul.vermeulen@vulcantech.com.au>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (13)

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group