Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: can't get rid of leading space in cell

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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?

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    What is the verdict?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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


  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •