can't get rid of leading space in cell

shammer

New Member
Joined
Feb 26, 2002
Messages
25
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Beautiful, thank you Aladin. That function works perfectly. Can I bother you for a simple macro I could place this in?
 
Upvote 0
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.
 
Upvote 0
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

<pre><font color='#000000'>
<font color='#000080'>Sub</font> ReplaceCode160()

<font color='#000080'>Dim</font> cl <font color='#000080'>As</font> Range

<font color='#000080'>For</font> <font color='#000080'>Each</font> cl In Selection
cl = Replace(cl.Text, Chr(160), "")
<font color='#000080'>Next</font> cl

<font color='#000080'>End</font> <font color='#000080'>Sub</font>


<font color='#000080'>Sub</font> ReplaceCode160b()

<font color='#000080'>Dim</font> cl <font color='#000080'>As</font> Range

<font color='#000080'>For</font> <font color='#000080'>Each</font> cl In Selection
cl = Application.WorksheetFunction.Substitute(cl.Text, Chr(160), "")
<font color='#000080'>Next</font> cl

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top