Removing Space

[Tom]

Hello - When I import data, there is a space in front of the first
character in a cell. Would like to know if there is a visual basic
code to remove this space. I already have a code which removes all
spaces, now need code to remove the first space and leaves the other
spaces in place.
Thanks,
Tom

 

[Beege]

Hello - When I import data, there is a space in front of the first
character in a cell. Would like to know if there is a visual basic
code to remove this space. I already have a code which removes all
spaces, now need code to remove the first space and leaves the other
spaces in place.
Thanks,
Tom

ltrim?

beege

 

[Dave Peterson]

Take a look at LTrim.

If you want to get right of the rightmost space characters, you'd use RTrim. If
you wanted to get rid of both leading and trailing spaces, you could use Trim.

And if you wanted to get rid of leading/trailing/duplicated internal spaces, you
could use application.trim.

 

[Tom]

Take a look at LTrim.

If you want to get right of the rightmost space characters, you'd use RTrim. If
you wanted to get rid of both leading and trailing spaces, you could use Trim.

And if you wanted to get rid of leading/trailing/duplicated internal spaces, you
could use application.trim.

Could you please put in code format. I'm not very real versed in
writing visual basic code.
Thanks,
Tom

 

[Dave Peterson]

Option Explicit
Sub testme()
dim myRng as range
dim myCell as range

with worksheets("sheet9999")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
mycell.value = ltrim(mycell.value)
'or
mycell.value = rtrim(mycell.value)
'or
mycell.value = trim(mycell.value)
'or
mycell.value = application.trim(mycell.value)
next mycell

end sub

 

[Richard O. Neville]

Spaces don't normally occur in Excel cells except in text entries. If your
problem is that they are really spaces, you can remove them with the Replace
function. Search for (type in a space) and Replace with (nothing--leave box
empty). Be sure not to delete spaces in the text entries. If they aren't
really spaces entered with the spacebar, you may have to look for a
different number format (Format-Cells).

 

[Tom]

Spaces don't normally occur in Excel cells except in text entries. If your
problem is that they are really spaces, you can remove them with the Replace
function. Search for (type in a space) and Replace with (nothing--leave box
empty). Be sure not to delete spaces in the text entries. If they aren't
really spaces entered with the spacebar, you may have to look for a
different number format (Format-Cells).






- Show quoted text -

I got Dave's code to run (though chugs a little???). When I tried
using the "Find Replace" it removed all spaces. Just wanted the first
spaces removed.

Thanks to both of you for helping me. Much appreciated.
Tom

 

[Dave Peterson]

If your data is in a single column, and you could get rid of leading and
trailing spaces, you could use:

Select the column
Data|Text to columns
Fixed width
(Remove any lines that excel guessed)
and finish up

If you have lots of columns, you can do the same thing for each column.

Record a macro when you test it manually to see the code.

(This'll be a lot quicker.)