How to get rid of annoying spaces 1

[GLENEE]

Is there a way of removing spaces from the middle of text fields:

examples:
"ABBG TP 13 AASR RLC"
"A (AD) TP 47 AD SQN RLC"
"MECH COY UK CSS STN"

I'm talking thousands of records so obviously the manual option is out of the question. I could use the find/replace option and replace space space space with one space etc, however the end user will not want to do this and besides the data will be read only to them. Ideally I would like to resolve it automatically on import of the records. Thanks

 

[Craig0201]

I'd use replace in an update query.

Import then run the query.

 

[TheAceMan1]

How are ya GLENEE . . .

Example:

[blue]   Dim Txt As String
   
   Txt = "A  (AD) TP        47 AD SQN RLC"
   Txt = Replace(Txt, "  ", "")[/blue]

See Ya! . . . . . .

 

[Golom]

Do you want to remove ALL spaces or just convert multiple spaces to a single space? In the first instance, use TheAceMan1's suggestion. If it's the second then write a public function

Public Function SingleSpaceIt(myString As String) As String
SingleSpaceIt = Trim(myString)
Do Until Len(SingleSpaceIt) = Len(Replace(SingleSpaceIt,"  "," "))
   SingleSpaceIt = Replace(SingleSpaceIt,"  "," ")
Loop
Exit Function

Then in your SQL

Select ..., SingleSpaceIt([FieldWithSpaces]) As [Newfield],

 

[TheAceMan1]

Golom . . .

Yeah . . . I forgot to loop . . .

[blue]GLENEE[/blue] . . . A cleaned up idea of what you want:

[blue]   Dim Txt As String
   
   Txt = "A  (AD) TP        47 AD SQN RLC"
   
   
   Do Until InStr(1, Txt, "   ") = 0
      Txt = Replace(Txt, "   ", "")
   Loop

   Txt = Replace(Txt, "  ", " ")
   Debug.Print Txt[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

GLENEE . . .

Had 4 test simulations.Copied the wrong one. Code should be as follows:

[blue]   Dim Txt As String
   
   Txt = "A  (AD) TP        47   AD    SQN                 RLC"
   
   
   Do Until InStr(1, Txt, "  ") = 0
      Txt = Replace(Txt, "  ", " ")
   Loop

   Debug.Print Txt[/blue]

See Ya! . . . . . .

 

[GLENEE]

Sorry i haven't responded, i've been away.
I want to replace multiple spaces with a single space. I've written the function suggested by Golom but not quite sure how i use it. Can you explain further the bit about using with my SQL.

Ex:
The Table name is "tblSites"
The Field with spaces is "txtName"

Thanks

 

[Melagan]

Why not use Trim() ?

SQL:

SELECT Trim([txtName]) as txtName_Trimmed

FROM tblSites

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Why not use Trim() ?
Because Trim don't care spaces in the middle of the string.

 

[GLENEE]

After importing the records I used an update query and Goloms function to achieve the result. Thanks.