Spaces in string only visible in VBA

[The_Greg]

Hello,

So, in Excel normal view the cell seems to be normal and in the 'formula input box' the text is without any spaces (before text). But in VBA I go through every cell and giving that cell value to variable it gives the result
" text text text". And that seems to be only one space. Trim and replace don't seem to work at all, so is there any other way to remove this invisible space?
Original excel file is taken through Access from SQL server.
Here the 'space' seems normal but in editing mode, it seems to be the length of 3-4 spaces.

[Andy Pope]

You really need to determine what the character code is for that 'space'.

you could try this in the immediate window. (ALT+F11 CTRL+G)

Please Login or Register  to view this content.

or in your code you could test the variable

Please Login or Register  to view this content.

[The_Greg]

Hi, I have never used immediate window, but msg ascw gives the value 9

[Andy Pope]

9 denotes the characters is a TAB

[The_Greg]

Any idea why it is only visible in VBA code?

[Andy Pope]

it's a print character.

if you use the immediate window again but this time write to an empty cell

Please Login or Register  to view this content.

On the worksheet the space is not visible.
Copy the cell to notepad and the tab will be replace with gap and the cursor will jump when you move along the text.
Copy it to word and then display paragraph marks you will see a dot.

Anyhoo what you need to do either remove the tab from the source tab or within your variable.

for variable removal you can use

Please Login or Register  to view this content.

[The_Greg]

Thank you very much, that worked!