Removing Space
 

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

Removing Space
 

Tom wrote:
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

Removing Space
 

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 wrote:

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

--

Dave Peterson

Removing Space
 

On Feb 26, 11:31 am, Dave Peterson wrote:
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 wrote:

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

--

Dave Peterson

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

Removing Space
 

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



Tom wrote:

On Feb 26, 11:31 am, Dave Peterson wrote:
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 wrote:

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

--

Dave Peterson

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

--

Dave Peterson

Removing Space
 

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" wrote in message
ups.com...
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

Removing Space
 

On Feb 26, 1:58 pm, "Richard O. Neville"
wrote:
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" wrote in message

ups.com...



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- Hide quoted text -

- 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

Removing Space
 

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.)

Tom wrote:

On Feb 26, 1:58 pm, "Richard O. Neville"
wrote:
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" wrote in message

ups.com...



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- Hide quoted text -

- 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