Macro to eliminate extra space

I've imported a query from an AS400 database, and in the column that I'm
using as my "sort", many of the entries start with a blank space. I can
left-justify, but there's still a blank space in some of them, which is
messing up my sort. Is there a macro that I can write to eliminate a blank
space at the beginning of any cell entry? Additionally, some cells begin
with a "<", or a "{" that is unnecessary. I'd like to be able to eliminate
those extraneous characters or blank spaces using a macro if possible.
Thanks for any help.

K-

Trimall macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------




"kleivakat" <[email protected]> wrote in message
news:[email protected]...
> I've imported a query from an AS400 database, and in the column that I'm
> using as my "sort", many of the entries start with a blank space. I can
> left-justify, but there's still a blank space in some of them, which is
> messing up my sort. Is there a macro that I can write to eliminate a
> blank
> space at the beginning of any cell entry? Additionally, some cells begin
> with a "<", or a "{" that is unnecessary. I'd like to be able to
> eliminate
> those extraneous characters or blank spaces using a macro if possible.
> Thanks for any help.
>
> K-
>
>

If those space characters, <, and { only appear as the first character in the
string, you could select the column and do 3 edit|replaces

select the range
what: (spacebar)
with: (leave blank)
replace all

followed by
what: <
with: (leave blank)
replace all

followed by
what: {
with: (leave blank)
replace all

You could get code by recording the macro when you do all 3 edit|replaces.

But if you can have any of those characters anywhere else in the cell, then you
could use a macro:

Option Explicit
Sub testme01()
Dim myCell As Range
Dim myRng As Range
Dim myStr As String

Set myRng = Selection

For Each myCell In myRng.Cells
myStr = Trim(myCell.Value)
Do
Select Case Left(myStr, 1)
Case " ", "<", "{"
myStr = Mid(myStr, 2)
Case Else
Exit Do
End Select
Loop
myCell.Value = myStr
Next myCell

End Sub



kleivakat wrote:
>
> I've imported a query from an AS400 database, and in the column that I'm
> using as my "sort", many of the entries start with a blank space. I can
> left-justify, but there's still a blank space in some of them, which is
> messing up my sort. Is there a macro that I can write to eliminate a blank
> space at the beginning of any cell entry? Additionally, some cells begin
> with a "<", or a "{" that is unnecessary. I'd like to be able to eliminate
> those extraneous characters or blank spaces using a macro if possible.
> Thanks for any help.
>
> K-

--

Dave Peterson

Down the tool ASAP. It's got tons of things you can used. Its free and user
friendly.
http://www.asap-utilities.com/index.php?page=news.php

"Dave Peterson" wrote:

> If those space characters, <, and { only appear as the first character in the
> string, you could select the column and do 3 edit|replaces
>
> select the range
> what: (spacebar)
> with: (leave blank)
> replace all
>
> followed by
> what: <
> with: (leave blank)
> replace all
>
> followed by
> what: {
> with: (leave blank)
> replace all
>
> You could get code by recording the macro when you do all 3 edit|replaces.
>
> But if you can have any of those characters anywhere else in the cell, then you
> could use a macro:
>
> Option Explicit
> Sub testme01()
> Dim myCell As Range
> Dim myRng As Range
> Dim myStr As String
>
> Set myRng = Selection
>
> For Each myCell In myRng.Cells
> myStr = Trim(myCell.Value)
> Do
> Select Case Left(myStr, 1)
> Case " ", "<", "{"
> myStr = Mid(myStr, 2)
> Case Else
> Exit Do
> End Select
> Loop
> myCell.Value = myStr
> Next myCell
>
> End Sub
>
>
>
> kleivakat wrote:
> >
> > I've imported a query from an AS400 database, and in the column that I'm
> > using as my "sort", many of the entries start with a blank space. I can
> > left-justify, but there's still a blank space in some of them, which is
> > messing up my sort. Is there a macro that I can write to eliminate a blank
> > space at the beginning of any cell entry? Additionally, some cells begin
> > with a "<", or a "{" that is unnecessary. I'd like to be able to eliminate
> > those extraneous characters or blank spaces using a macro if possible.
> > Thanks for any help.
> >
> > K-
>
> --
>
> Dave Peterson
>