finding tab characters in a table

[adgorn]

I often copy and paste data into my access table. I've noticed that may include what seems to be "tabs" like from a Word document. I want to find and get rid of those. Cntrl F searching for ^t doesn't do it. Any tips? Thank you!

[CJ_London]

not sure if it will work with word formatting but you could look at using the plaintext function

myplaintext=plaintext(myformattedtext)

[Micron]

Post some data and explain what the table field data type is (including whether or not it is a RTF field). If you're just asking about the caret then I know it can be found in a field that just contains regular text. If you're asking about other characters such as tag delimiters <, >, that can be trickier.

[adgorn]

Example: "Glen Oaks Ragged Stranger d June 21 1920"

FYI When I look at the above in Word I see the little arrows indicating tabs. In other words, I copied this text with tabs from a Word doc into the Access field. It's a plain text field.

[CJ_London]

I don't see any indication of a tab type character in your example unless the solitary d is it?

you need to determine the ascii code of the character(s) and then use the replace function to replace them with a zls (zero length string). You may find that what you see as a single character is actually made up of one or more non printing characters.

An alternative would be to copy the text to notepad, then copy that to access.

[Micron]

Posting from a Word document is not what I had in mind. Posting from an Access table is what I had in mind. I also had in mind that you would tell us what the data type of the field is - assuming you're even looking at a table field, but you didn't respond to that inquiry.

As noted, you can eliminate RTF characters by copying into Notepad, but whether or not that makes sense depends on where the data is coming from or what the data type of the target field is. AFAIK, RTF characters should not be coming over from an external source such as a document IF the target field is plain text. If that is true, then copying into Notepad shouldn't be required unless it's just to test. I'd guess you don't want to have to do that umpteen times and field by field.

[adgorn]

Trying again:

Glen Oaks Ragged

The tab is between Oaks and Ragged. This comes from a plain text field in my Access table. What I am trying to do is find all instances like this in my table so I can identify where I have to work to eliminate.

[Micron]

The reason for some confusion is (at least for me) that
- you mention tabs in Word (which are really tab stops, an electronic representation of a mechanical tab stop on a typewriter)
- and have shown the caret as an example of what you want to get rid of (or at least that's an interpretation)
- and there isn't anything to show the difference between Glen and Oaks or Oaks and Ragged in your post. Possibly code tags here would have fixed that.
- tabs in Access are something else altogether

If I go by the email I received I think you want to remove about 5 spaces between 2 words. If it's a plain text field as you say, my guess is that the tab character (ascii 9) has been converted to spaces. Check that by placing your cursor at the end of Oaks and see what happens if you press the right arrow key. If it moves one space, you can use Replace to get rid of the extra spaces. If it jumps over the space, it is in fact a tab character and should be able to be removed by referencing chr(9).
So either

Replace("myString", "chr(9)","") (not sure about the quotes around chr(9) or
Replace("myString","     ","") assuming there are 5 spaces.

You can see that by using code tags here I am able to maintain the extra spaces.

[adgorn]

I appreciate your working with me on this. I know that I probably copied this phrase from a Word document that had a tab character and then pasted into my plain text field in Access. This is what I see when I now do the reverse and copy out of Access and paste back into Word:

There is actually a space and a tab character in between. I'm trying to find all instances in my data table where this kind of thing is occurring so I can get rid of those tab characters.

When I put the cursor at the end of Oaks and go right it moves twice - one for the space and once for the tab. So I think I need to find all the chr(9)'s so I can replace them with a real space. I don't seem to be able to do that using the right click Find command.

[orange]

?? Not sure exactly what you have and need. I copied your sample into Notepad++, it showed up as plain text.
That is no Tabs, Tabs were changed to spaces.
I then put Tabs in in Notepad++, copied this text and put it in str variable in VBA code below.
On testing, again the Tabs were converted to spaces(asc(32)).
So I forced a Tab asc(9) as below.

Sub FindTab()
    Dim str As String
    Dim i As Integer
    str = "Glen" & Chr(9) & "Oaks    Ragged      Stranger d June 21 1920"

    For i = 1 To Len(str)
        Debug.Print Mid(str, i, 1); Asc(Mid(str, i, 1))
    Next i
End Sub

Output:

G 71 
l 108 
e 101 
n 110 
     9 <<-------------This is the TAB character.
O 79 
a 97 
k 107 
s 115 
  32 
  32 
  32 
  32 
R 82 
a 97 
g 103 
g 103 
e 101 
d 100 
  32 
  32 
  32 
  32 
  32 
  32 
S 83 
t 116 
r 114 
a 97 
n 110 
g 103 
e 101 
r 114 
  32 
d 100 
  32 
J 74 
u 117 
n 110 
e 101 
  32 
2 50 
1 49 
  32 
1 49 
9 57 
2 50 
0 48

[Micron]

I don't seem to be able to do that using the right click Find command.

If it's possible, I don't know how either. That's why I was suggesting the Replace function. You'd use it in an Update query; something like
UPDATE myTable SET myTable.myField = Replace("myField", chr(9), "");
or
Replace("myField", chr(9), " ")
As I mentioned, not sure but I don't think quotes should be around chr(9)

[davegri]

How about doing it the other way around - only allow desired characters, skipping anything else?
You can add additional allowable characters by adding them to the Case clause.

Public Function fcnMakeClean(arg As String) As String
    Dim i As Long
    Dim j As Long
    Dim rslt As String
    arg = Trim(arg)
    j = Len(arg)
    For i = 1 To j
        Select Case Asc(Mid(arg, i, 1))
            'only allow these characters
            Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z
                rslt = rslt & Mid(arg, i, 1)
            Case Else   'convert to space
                rslt = rslt & space(1)
        End Select
    Next i
    'convert multiple spaces to single
    Do While InStr(1, rslt, Space(2)) > 0
        rslt = Replace(rslt, Space(2), Space(1))
    Loop
    fcnMakeClean = Trim(rslt)
End Function