Removing a 'tab' from inside a cell's contents

[tvoltagg]

So I get some quirky data from a customer, where some of the fields look like this (brackets are MINE):

[RES]

and some look like this (brackets are MINE):

[R""ES ]

I figured I'd just replace out the double-quotes and spaces to get it to be consistent, but apparently there is a tab between the double quotes. If I manually go into one of the cells, put the curson after the 'R', and hit the delete key, the first quote goes away. If I hit the delete the key again, though, the spaces at the END go away. If I hit the delete key a third time, the 2nd quote goes away.

Does anyone know how to use the Ctrl-H feature to remove embedded tabs? I cannot use text-to-columns as not all of the rows have the same quirky stuff.

Thanks!

 

[Richard Schollar]

You can input non-printing characters eg tabs into the Find What of the Edit/Replace dialog by using the character's relevant Unicode value and the numeric keypad. In find what, try holding down Alt and, using the numeric keypad, typing in 009.

EDIT: alternatively, the CLEAN function can be used to remove them too eg =CLEAN(A1)

 

[tvoltagg]

thanks so much for the quick response..

The CLEAN function works, but would be a bit bulky for what I'm trying to do.

When I hit ALT-009 in the find what box, nothing was entered. Any ideas why?

 

[Richard Schollar]

Hmm, I must admit that I currently can't enter Ascii characters 1-31 from the numeric keypad (this is not a normal state of affairs, but all my attempts to rectify have come to naught) but you can get around it easily enough: when you have this sheet open on your screen, open up the VBE (Alt+F11) and then open up the Immediate Pane (Ctrl+G - should appear in the bottom right of the Visual Basic Editor). Paste the following in there and press return:

cells.Replace chr$(9),""

 

[tvoltagg]

Hmm.. that doesn't work either.

I'm using the replace function in my code, but it can't seem to locate the tabs. I suppose it's possible they aren't tabs..? I've copy/pasted one of the cells below..

"R"" ""ES" (pasted the cell)

R" "ES (pasted the cell from the formula bar)

 

[tvoltagg]

Looks like the form on this site removes the tab though..

 

[tvoltagg]

Ah, got it to work, but had to remove the $.
Cells.Replace chr(9), "'

thanks so much!

 

[Richard Schollar]

You can use the following code to identify what characters are making up your string:

Sub Give_Codes() 
    Dim b() As Byte, i As Long 
    If Selection.Count > 1 Then MsgBox "You must select a single cell": Exit Sub 
    b = StrConv(Selection.Value, vbFromUnicode) 
    With Worksheets.Add 
        .Cells(1, 1) = "Ascii Code": .Cells(1, 2) = "Character" 
        For i = 1 To UBound(b) + 1 Step 1 
            .Cells(i + 1, 1) = b(i - 1) 
            .Cells(i + 1, 2) = Chr$(b(i - 1)) 
        Next 
    End With 
End Sub

Select the cell containing the string and run the code - it'll dump the results into a new sheet.