Removing leading spaces

[jmdaniel]

I am converting an Oracle report to Excel, and have run into an issue. I have
changed the macro to pull a different column than before. The data still
resides in the O column, but is different, from a trailing and leading zero
standpoint. The portion of the macro that worked before:

' Removes leading spaces column O
Columns("O:O").TextToColumns Destination:=Range("O1"), _
DataType:=xlFixedWidth, OtherChar:="|", _
FieldInfo:=Array(Array(0, 2), Array(7, 1))

the cells in this column had 6 leading, and 4 trailing spaces. Now, when I
pull a different column of data from Oracle, but put it in the same column in
Oracle, I get the "Do you want to replace the contents of the destination
cells?" question, which I did not get before. If I answer yes, it errors out
on the next step in the macro, which attempts to put the data in a pivot
table format. It won't work, because the macro has deleted the column
heading. Again, something that didn't happen before. The onle difference I
can see is that the new column I am trying to bring in has 6 preceding
spaces, no trailing, instead of the 6 leading and 4 trailing of the old
column. Evidently, I am clueless, and could use a hand....

 

[Bernie Deitrick]

JM,

If they are really spaces, and you don't have any interior spaces to worry
about, then you could use the line:

Columns("O:O").Replace What:=" ", Replacement:="", LookAt:=xlPart

Otherwise, you will need to re-record a macro that does what you need it to
do.

HTH,
Bernie
MS Excel MVP

 

[clane]

you might also be able to use the text to colums feature to remove th
leading spaces its under Data --> Text to colums then just eithe
delimit it with spaces (if there are no spaces in side the data) or us
the fixed width delimiting and you can get rid of the leading spaces bu
the ending spaces might not work unless your data all has the sam
lenght of character

 

[jmdaniel]

Genius, Bernie. Thanks for the help. Thanks to you too, Clane, for taking a
look at it.