trim

[Guest]

I have a field of data imported from another application. All data is
prefixed by a space. How can I get rid of this space? I saw in help
something about 'trim' but how do I use it? Thanks.

 

[Gord Dibben]

TRIM Function can be used with actual text or a cell reference.

=TRIM(" two spaces ") returns the text "two spaces"(no quotes) with no
leading or trailing spaces.

=TRIM(cellref) returns whatever is in cellref and strips leading and trailing
spaces.

Assuming column A to have a range of these data.

In B1 enter =TRIM(A1)

Drag/copy down to bottom of Column A range.

Select column B and Copy then Paste Special(in place)>Values>Ok>Esc.

Delete original Column A.

Gord Dibben Excel MVP

 

[Guest]

Thanks for your help. Unfortunately, its still not working. Could the
problem be the program (a 'custom' warehouse database program) that I'm
getting the info from?
mtress

 

[David McRitchie]

You probably have a required blank space (  in HTML). You could
convert them to spaces manually by using Alt+0160 on numeric keypad,
or if you have a laptop Fn+Alt+0160 on the numeric keypad.

But it is a lot easier to install a macro into your personal.xls and use it anytime
you need it. See TrimALL in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
instructions at the top of the page point you to
http://www.mvps.org/dmcritchie/excel/getstarted.htm
if you are not already familiar with installing and using macros.

Whether you install the macro or not instructions to find out what you have
can be found at the above location (#trimall and #debugformat)

 

[Gord Dibben]

HTML has a habit of leaving junk in cells. Non-breaking spaces for one.

To clean up the junk you can use TRIM and CLEAN and Edit>Replace if you know
which character it is.

Download Chip Pearson's CELLVIEW add-in from

http://www.cpearson.com/excel/CellView.htm

Load through Tools>Add-ins.

Select the data and Chip's add-in will show you what the characters are.

If carriage returns you may be able to Replace them using ALT + 0010 or 0013

Most likely David McRitchie's TRIMALL macro will be of greatest assistance.
Cleans up what TRIM and CLEAN miss.

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


Gord Dibben Excel MVP