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