Replace Function with Left function

[Khalil Handal]

@Khalil

I suggest you confirm your requirement since it seems to be moving as others have suggested.
Test, confirm and post a few examples of the revised requirement.
You might even describe what these text strings represent and why the format is so critical.

Hi,
I think the word "moving" used was something I was not aware off, so when deleting spaces let us say 2 spaces this means that the first character after the space is moved to a new position which is 2 characters less.

I made a mistake in one of my examples . It should be like this
User input: 27265 SAM 33
output: 27265SAM33
How it should be: 27265SAM 33



So, let me say it different words:
The string starts with numbers followed by letters. I need to keep the spaces that are after the letters and remove the spaces before these letters.

In this previous example:
The letter S was at the position 7, after removing the space, the letters S became at position 6. Is this of any importance?

Another example:
User input: 276 SAM 33 (4 spaces after number 6, the S is at position 8)
output: 276SAM33
How it should be: 276SAM 33 (the letter S is at position 4) Is this of any importance.

Khalil

[pbaldy]

The term moving was used (by me anyway) to refer to your requirement, not the characters moving. You initially specified

I want to remove spaces in the first 14 characters and keep the rest

which is relatively simple and a solution given. Then the requirement changed to a variable number of characters, determined by the last alpha character. I'd solve it with a function as described in post 11.

[Micron]

If you put the below function in a standard module it seems to work with all of the posted examples:

Function RemSpaces(strIn As String) As String
Dim n As Integer

bolEnd = False
For n = 1 To Len(strIn) - 1
  If bolEnd = True Then Exit Function
'Debug.Print Mid(strIn, n, 1)
   If Mid(strIn, n, 1) = " " Then
    If Not IsNumeric(Mid(strIn, n + 1, 1)) Then
      strIn = Replace(strIn, " ", "", , 1)
      RemSpaces (strIn)
    End If
   End If
Next
Debug.Print strIn
bolEnd = True

End Function

You need Dim bolEnd at the top of the module, as in
Option Compare Database
Option Explicit
Dim bolEnd As Boolean

You could call it in testing as remspaces "262 74 RTC 7"
or put it in a calculated query field such as FieldAlias: RemSpaces ([queryFieldNameHere]) and it should return the "fixed" string to the query.
As some would say, 'there's your fish'.

[pbaldy]

Recursive call, interesting. I went a different way, but both work. Well, mine works, yours doesn't yet.

[Micron]

Well, mine works, yours doesn't yet.

Don't know if that means it doesn't work for you, or you hadn't tried it yet.
In case anyone reading doesn't grasp why the recursive function, it's because the length of the string changes as you remove spaces, thus subsequent Mid expressions and the counter are no longer valid. Calling the function again but passing the new string was my approach. Without the boolean flag, when the inner pass finishes, the outer function picks up from where it left off. The altered string may cause the rest of the outer pass to do no more processing but the debug will print again without the boolean. I left it that way for testing purposes but it's not necessary if used in a query. To use the function in a query would require at least one forgotten thing - to set the return value:
...
Debug.Print strIn
bolEnd = True
RemSpaces = strIn

And to use only in a query, I don't think the boolean flag is required at all since dropping the debug.print and setting the function return value should be enough.

[pbaldy]

Don't know if that means it doesn't work for you, or you hadn't tried it yet.

To use the function in a query would require at least one forgotten thing - to set the return value:

I was trying to make a joke, you hadn't set the return value.

I did it without recursion, using a variable to keep track of the return value.

[pbaldy]

I want to apologize for this remark:

mine works, yours doesn't yet.

It was intended to be a funny way of pointing out that the return value hadn't been set. It didn't come off that way. Micron's function worked flawlessly in my testing, (s)he just hadn't set the return value. Sometimes when I try to be funny I fall flat on my face, as happened here, and I apologize.