Removing whitespace from a string

[yumbelie]

Hiya, what I am trying to do is concatenate a string without it leaving white space.

I've tried the Trim() function, but it hasn't removed it.
What I have currently is:

MyWorkbook.Sheets("Sheet" & Str(x)).Name = newnamearray(x)

Problem is, the result of "Sheet" & Str(x) is "Sheet 1" - not "Sheet1". I can't figure out how you can concatenate a string without the white space, which is irratating. any help appericated.

 

[TonyJollans]

Hi,

You say you've tried the Trim function but you don't say where - you need to use it on the result of the Str function (which, somewhat oddly to my mind, generates the space to allow for the implied plus sign).

Try ...

MyWorkbook.Sheets("Sheet" & Trim(Str(x))).Name = newnamearray(x)

Enjoy,
Tony

 

[Zathras]

You can also not use Str function at all:

  MyWorkbook.Sheets("Sheet" & x).Name = newnamearray(x)

Str( ) leaves space for a possible minus sign. It's a subtle point, but if you use the plus sign for concatenation, then you need to use Trim(Str(x)), but with the ampersand for concatenation you don't.

Not really surprising when you consider that the plus sign came out of Basic, while Excel uses the ampersand when concatenating in a cell formula. So it is consistent, sort of.