extra space

[Guest]

I'm running a macro to compare to sets of values, but it's not working because after one imported strings has a space on the end it

Grateful for any ideas to get round this problem.

 

[Leo Heuser]

MAx

Check out the RTrim-function.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

I'm running a macro to compare to sets of values, but it's not working

because after one imported strings has a space on the end it.

 

[Pete McCosh]

Max,

it kind of depends on where you're getting the data from
and what you're diong with it. However, you can use the
TRIM function to remove any leading or trailing spaces,
either as a worksheet formula, which you can then use as a
basis for your comparison:

=Trim(B1)

Or in your macro to manipulate the cell value:

for X=1 to 10

If Application.WorksheetFunction.Trim(Cells(2,x).value)= _
Cells(1,x).Value Then
... your code here...
End If

Next X

Cheers, Pete.

-----Original Message-----
I'm running a macro to compare to sets of values, but

it's not working because after one imported strings has a
space on the end it.

 

[Jean-Yves]

Hi Max,

You can use the Trim function to remove leading and trailing blanks (before
and after - not in the middle)
Regards,
Jean-Yves

I'm running a macro to compare to sets of values, but it's not working

because after one imported strings has a space on the end it.

 

[David McRitchie]

Hi <insert real name here> aka MAx,
Did you really check the code or does it just look like a space.
If you got the data from HTML you almost definitely have
&nbsp; a code(160) nonbreaking space character.
See TRIMALL macro for details
http://www.mvps.org/dmcritchie/excel/join#trimall