Removing a Space, If There is More Than One

[Mordred]

Hi all,

Firstly I would just like to express my frustration with regards to people who's job is data entry. Inconsistencies are very frustrating.

My problem is this: I have to compare dates that come in this format "mmmm yyyy". No problem but as of this month's update, the source has entered their date like this: "October 2010". There are two spaces between November and 2010 instead of the normal one space. Because of this, the code I implemented does not read the cell and skips it entirely. I would prefer the code fix the source error if it exists for consistency's sake. Is it possible to look at a cell and remove an extra space if it exists between two values?

PS. I know we are all human and data entry can be quite tedius and therefore prone to error but from a programming point of view these little inconsistencies are a pain in the behind.

Many thanks

[Toddneyx]

So you're saying some fields have these two spaces and some don't? Do some have 3 spaces? If not, can't you just do a find for "spacespace" and replace all with "space"? Not using the actual words but actual spaces.

[TMS]

What format is the input data? A cell in a worksheet? Is it formatted as text?

Having just tried typing in November, any number of spaces, and 2010, Excel still recognises it as a date and displays it as Nov-10 with 01/11/2010 in the formula bar.

Which begs the question, if you convert the double space to a single space, will it be a date or will it still be text?

A sample workbook might help.

Regards

[DonkeyOte]

Without knowing your overall aim you might wish to utilise TRIM

Note: in VBA terms you would use either Application.Trim or the WorksheetFunction.Trim (directly or via Evaluate)
VBA Trim Function does not remove superfluous spaces mid string unlike it's "native" counterpart.

[Mordred]

@TMShucks: I copy and paste a table from a website into a staging page in a workbook. From there, code is run to move the data into our worksheets. I'll create a mock up with a couple of sheets for a reference. At work we use Office 2003.
@Toddneyx: The data usually comes in with one space but for all I know (in the future) there could be 4 spaces.

Give me a couple of shakes and I'll have the workbook uploaded.
Thanks

[Mordred]

Here is the workbook and thanks. I'm out for a couple of hours so don't feel the need to rush or anything. Thanks again.

[Leith Ross]

Hello Modred,

I agree with Donkey that using the "native" methods. However, if you are inclined to learn about another way, there is. This method removes the extraneous spaces using Regular Expressions. I added a function to clean the spaces of a string using this method. The code has been added to your macro below.

Please Login or Register  to view this content.

[Mordred]

Thanks Leith, my problem is that I haven't played around with the functions on the spreadsheet side of things. Thus far I've been an eager beaver with regards to coding. I've seen some of the formulas used on the function side of things and a lot of it looks like gobbleDeegook to me. I am going to try your code and thank you for taking the time.

[Mordred]

I think I am going to have to go at this some other way because while the code works, it is still not referencing the proper cell. I think it is because the initial search skips it. I would need the trim function explained to me though but, should I start this in Excel General instead?

[Leith Ross]

Hello Mordred,

I am not sure why you would want to start a new thread, especially in the General forum. Your question is about programming is it not?

[Mordred]

I'm going to mark this as solved and do some learning over the next little while some ins and outs of spreadsheet functions. Thanks all.

[Mordred]

It is but I thought general dealt more with the spreadsheet side of things.

[DonkeyOte]

Just to be clear - my points re: TRIM related to VBA specifically.

Please Login or Register  to view this content.

If you review the output of the above you will find the first differs from the remainder - ie the VBA Trim Function ignores superfluous spaces mid string.

the last two are effectively the same thing, however, you can adapt Evaluate to process/manipulate ranges simultaneously (ie avoid iteration)

[Mordred]

I tried your example Donkey and it works exactly the way you code it but, when I apply it to my own scenario:

Please Login or Register  to view this content.

it does not work. Why?

[DonkeyOte]

By "does not work" are you saying that the debug.print results are the same as the original ?

If that is indeed what you're saying - does the below return expected result ?

Please Login or Register  to view this content.

If not, what about if you change 160 in the above to 10 ?
(Char 160 is a non breaking space [not a space per se] - Char 10 is a line break)

[catjoke]

I would like to give a code which can be re-used at anywhere.
When I study VBA in Excel, I wonder there is NO strtran function like VFP.
Thus, I wrote it as VBA version in 10+ years ago.
For your usage, very simple to ask space to nothing.

Please Login or Register  to view this content.

[Mordred]

By "does not work" are you saying that the debug.print results are the same as the original ?

If that is indeed what you're saying - does the below return expected result ?

Please Login or Register  to view this content.

If not, what about if you change 160 in the above to 10 ?
(Char 160 is a non breaking space [not a space per se] - Char 10 is a line break)

Yes, the debug.print results were the same as the original but your code worked like a charm. I've tweaked it a little

Please Login or Register  to view this content.

so that a search will find the proper cell now (which reads Oct-2010 now instead of October 2010. Thank you so much DonkeyOte.