can't get rid of leading space in cell

[shammer]

I have tried four things but for some reason they do not work. I have cut and pasted a column of numbers into excel, unfortunately they have a space in the field before the number begins.

I tried to convert the column by using =trim(cell) command. Did not work.

I tried to use the Text to Columns function but could not get it to work.

I used the Replace function by Finding " " and replace with "". That did not work.

Tried a version of someone's macro here, but only ended up deleting entire number in cell when it recognized a blank space in the cell.

I am thoroughly confused. Can someone provide some help please?

[Aladin Akyurek]

On 2002-04-10 12:14, shammer wrote:
I have tried four things but for some reason they do not work. I have cut and pasted a column of numbers into excel, unfortunately they have a space in the field before the number begins.

I tried to convert the column by using =trim(cell) command. Did not work.

I tried to use the Text to Columns function but could not get it to work.

I used the Replace function by Finding " " and replace with "". That did not work.

Tried a version of someone's macro here, but only ended up deleting entire number in cell when it recognized a blank space in the cell.

I am thoroughly confused. Can someone provide some help please?

Would you apply the following and report the result:

=CODE(LEFT(A1))

where A1 houses the (freshly) pasted ominous entry?

[shammer]

Every cell returns a number 160 when I apply =CODE(LEFT(A1)).

What is the verdict?

[Mark W.]

On 2002-04-10 12:21, shammer wrote:
Every cell returns a number 160 when I apply =CODE(LEFT(A1)).

What is the verdict?

It's not a space... that would be 32.

[Aladin Akyurek]

On 2002-04-10 12:21, shammer wrote:
Every cell returns a number 160 when I apply =CODE(LEFT(A1)).

What is the verdict?

=SUBSTITUTE(A1,CHAR(160),"")

should clean the culprit. If the entries are supposed to be numbers, use:

=SUBSTITUTE(A1,CHAR(160),"")+0

Copy the cells of this formula and execute Edit|Paste >Values, and delete the original column.

Aladin

[Yogi Anand]

On 2002-04-10 12:21, shammer wrote:
Every cell returns a number 160 when I apply =CODE(LEFT(A1)).

What is the verdict?

Hi Shammer:
if you have your value with a leading space in cell G49,use the following formula in an adjacent cell:

=REPLACE(G47,1,1,"")

This will get rid of your leading space!

[shammer]

Beautiful, thank you Aladin. That function works perfectly. Can I bother you for a simple macro I could place this in?

[codyswan]

I used a different method for numbers which somehow were imported as text. The numbers usually align left instead of right in a column when this happens. Some formulas give a correct result off of such numbers and others don't. Very irritating.

If the numbers which aren't quite numbers start in A1, put this formula in B1=A1+1-1. Copy it down as many rows as you need.

Then copy and paste the values into A1. All problems with misalignment and leading or trailing spaces disappear. You have a pure number left.

This incredibly simple brute force method is great for very large amounts of not quite numerical data. I've done it on over 200,000 cells at once and it calculates almost immediately.

[Russell Hauf]

Are you using Excel 2000 or '97? (want to know for macro)...

[Russell Hauf]

The first function will work with Excel 2000 or later, and the second will work with '97 also. The only stipulation is that you have selected the range where you want to replace the (code 160) spaces before you run these (and of course you can modify to do whatever you want). Hope it helps,

Russell

Sub ReplaceCode160() 



    Dim cl As Range 

    

    For Each cl In Selection 

        cl = Replace(cl.Text, Chr(160), "") 

    Next cl 



End Sub 





Sub ReplaceCode160b() 



    Dim cl As Range 

    

    For Each cl In Selection 

        cl = Application.WorksheetFunction.Substitute(cl.Text, Chr(160), "") 

    Next cl 



End Sub