How can I remove an indent within a cell? For example, I have a column of
data that has several different indents some with no indent. How do I remove
all of the indents? When I look at the formula for cells with indents, I see
=' XYZ5

My data appears in the column as follows

XYZ5
XYZ4
XYZ3
XYZ2

I would like all of the data to be left justified w/o having to manually go
in and delete the ' from the cells.

Many thanks,
DB

Insert a column to the right of the cells displaying indents, in the first
cell of the newly inserted column enter the formula =Trim(CellAddress), where
cell address is the cell directly to the left of the current cell.

Copy the formula down the column as far as necessary. Copy the formula in
the inserted column, move to the top of the column to its left and do a
Paste/Special and select Values.

When finished delete the column with the trim formula in it.


--
Kevin Backmann


"DB--less than excel expert" wrote:

How can I remove an indent within a cell? For example, I have a column of
data that has several different indents some with no indent. How do I remove
all of the indents? When I look at the formula for cells with indents, I see
=' XYZ5

My data appears in the column as follows

XYZ5
XYZ4
XYZ3
XYZ2

I would like all of the data to be left justified w/o having to manually go
in and delete the ' from the cells.

Many thanks,
DB

If the data is as you have written it there is a space at the begining
of the text. You could:

1) Create a seperate column and use the trim function eg trim(a2) and
copy it down. Select all and then paste values

or

2) Select all the data edit, replace space (pressing space once in the
find box) with nothing. If Their are multiple spaces this can be
repeated a few times till they disappear, but if genuine spaces appear
in the middle of a text string you could loose them eg firstname
secondname

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=513733

In a helper column to the right use,

=TRIM(A1)

where A:A is your values. Copy down the list. Then Paste Special
Values over the old text. Delete the helper column.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513733

Thanks that did it.

"Kevin B" wrote:

Insert a column to the right of the cells displaying indents, in the first
cell of the newly inserted column enter the formula =Trim(CellAddress), where
cell address is the cell directly to the left of the current cell.

Copy the formula down the column as far as necessary. Copy the formula in
the inserted column, move to the top of the column to its left and do a
Paste/Special and select Values.

When finished delete the column with the trim formula in it.


--
Kevin Backmann


"DB--less than excel expert" wrote:

How can I remove an indent within a cell? For example, I have a column of
data that has several different indents some with no indent. How do I remove
all of the indents? When I look at the formula for cells with indents, I see
=' XYZ5

My data appears in the column as follows

XYZ5
XYZ4
XYZ3
XYZ2

I would like all of the data to be left justified w/o having to manually go
in and delete the ' from the cells.

Many thanks,
DB

If it actually text that was indented then you can use the decrease indent
button.
You can also downloas the ASAP utility http://www.asap-utilities.com/ it is
free and has a tool to remove leading and or trailing spaces.

"SteveG" wrote:


In a helper column to the right use,

=TRIM(A1)

where A:A is your values. Copy down the list. Then Paste Special
Values over the old text. Delete the helper column.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513733