Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

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

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Shammer,
    I am an old programmer, but new to the Excel VBA world. I have a very similar problem with B2B data from the Web that has formatting characters included along with the data. My point is I would suggest you reverse the test. Testing for 160 may work today, but down the road you will discover a 161 or something else weird. Test for the characters you want a toss the rest. I have included my macro for decimal numbers. Maybe you can adapt this for your problem.

    If the cruddy data is in A1 out to the right in an unused column I place =trim_dec(A1)+0 the +0 turns a clean string into a number.

    Public Function trim_dec(strIn As String) As String
    Dim strOut As String
    Dim lngInPtr As Long

    For lngInPtr = 1 To Len(strIn)
    If "." = Mid$(strIn, lngInPtr, 1) Then
    strOut = strOut & "."
    Else
    If Mid$(strIn, lngInPtr, 1) >= "0" And Mid$(strIn, lngInPtr, 1) <= "9" Then
    strOut = strOut & Mid$(strIn, lngInPtr, 1)
    End If
    End If
    Next
    trim_dec = strOut
    End Function

    This is a crude, but very effective filter.
    Of course if you improve it I would love to see you version. (or anyones)

    HTH
    Rocky...


    [ This Message was edited by: Rocky E on 2002-04-10 18:46 ]

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

    Default

    Thanks to all for their help. The version that worked for me was the second one on Russell's list. This will be a tremendous time saver for me. Hope to return the favor to someone as my knowledge increases.

  3. #13
    New Member
    Join Date
    Oct 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can't get rid of leading space in cell

    You are the best.. thanks alot..

  4. #14
    New Member
    Join Date
    Oct 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: can't get rid of leading space in cell

    Quote Originally Posted by Aladin Akyurek View Post
    =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

    You are the best.. thanks Aladin

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
  •