Remove spaces, but not all, from within a string 3

[jaycast]

I am trying to truncate all but two spaces from a field containing customer names:

IE: "JOHN T SMITH" should be "JOHN T SMITH".

The number of spaces separating the First Name and Middle Initial is variable.

I've thought of ways to do this, but my brain is at a stopping point. I'm stumped.

Can anyone clue me in? Remember, I need to retain the two spaces within the name.

Thanks

 

[zevw]

I answered someone yesterday in regards to how to find invalid characters.

look at thread705-638913

You will also need the mid function and look for spaces instead of valid characters.

If you cant figure it out I will write the code for you. But that thread should help you.

 

[Norris68]

The following function will strip any multiple spaces to a single one.

Public Function SingleSpace(InText As String) As String
Dim S As String
S = InText
Do
S = Replace(S, " ", " &quot
Loop Until InStr(1, S, " &quot = 0
SingleSpace = S
End Function

In effect, keep swapping double spaces for single ones until you can't find any more.

You can then use it in a SQL statement such as:
SELECT SingleSpace(CustName) FROM tblCustomer
or
UPDATE tblCustomer SET CustName=SingleSpace(CustName)

 

[zevw]

Thanks Norris68!

I never knew this!

Star for you!

 

[jaycast]

Norris68,

Worked like a charm. Chalk another star up there for ya!

 

[SeeJane]

Hi -

You could use the InStr function to look for instances of " " (double space) and then the Replace function replace them with " " (single space). Something like this (I haven't tested it, but you get the general idea)...

Function RemoveExtraSpace(YourString as String) As String

Dim intP as integer
Dim strEdit As String

strEdit = YourString

'Get 1st Char position of double space (" &quot
intP = InStr(1, YourString, " &quot

'replace, get new position of double space (" &quot
Do While Not intP = 0
strEdit = Replace(strEdit, " ", " &quot
intP = InStr(1, strEdit, " &quot
Loop

YourString = strEdit


Good Luck, CJ