Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    UK
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When partially editing an existing cell, whether in cell or not, the original untouch content is corrupted, e.g. "28 SPARE (OP. INFO)"
    is the original contents. I want to replace everything but the number and subsequent space, so I highlight the remainder of the cell and begin typing RIGHT the cell changes as foillows
    "2RIGHT)"
    shown in the actual cell. In the command line the new data is slightly lower than the old data. and partial characters are shown. When I try to re-insert the character 8 it is put to the left of the 2. Cell format is text and was imported from a MS-DOS text file. font is Arial. Although it is not visible in the cell, when I copy and paste it here there is a leading character - TAB I think.
    " 30 SPARE (OP. INFO)"
    and from teh " the format appears to be text although I tried changing to General. HELP!
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On my computer, the tab appears as a small square, indicating an unknown character.

    If you want to remove the tab at the beginning of some cells, you could use the following formula in H1:
    =IF(CODE(C1)=9,MID(C1,2,9999),C1)
    Then copy it down, then copy/paste special it as text back to column C.

    This will not fix the first row (where the tab is in the 2nd position)
    This eco-post is made of recycled electrons

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Definitely caused by a TAB Character
    at the end of the rows in Col C

    If you put this Macro into a Module
    Then Highlight the Cells in C and Run it,
    it will strip the Tabs and behaviour gets back to normal

    Code:
    Sub StripTab()
    
    Dim rngC As Range
    
    For Each rngC In Selection
        rngC = Replace(rngC, Chr(9), "")
    Next
    
    MsgBox "Tab Characters Cleared", vbExclamation
    
    End Sub
    Andrew

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A method that harnesses Excel's Replace functionality. (Ctrl+H if doing it manually). With a very large range, it should be faster that the For Next methodology shown by AKW..

    Code:
    Sub ReplaceTab()
        Range("c:c").Replace What:=Chr(9), Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows
    End Sub

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Yep, good call.

    I'd go with the ReplaceTab() routine, not mine
    Andrew

Posting Permissions

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