How to remove spaces from inside a string 1

[ruthcali]

i am using access97.

i have a table with 795 records. A field has spaces inside it and i need to remove the spaces.

ex: the fields looks like:
o3-lnw -30e -0003 or
3x-aco -bn86-0001 or
3m-bcds-30e -0001


all fields have:
-2 characters,
-then a dash,
-then 3 or 4 characters (if only 3 char., then the 3 char. and a space, if 4 char., then the 4 char. and no space)
-then a dash,
-then 3 or 4 characters (if only 3 char., then the 3 char. and a space, if 4 char., then the 4 char. and no space)
-then 4 characters.

and i need to make the fields look like:
o3-lnw-30e-0003
3x-aco-bn86-0001
3m-bcds-30e-0001

thanks,
ruth ruth.jonkman@wcom.com

 

[kerrigirrl]

if the spaces are ALWAYS in the space spot (if there is one) then try this:

if mid(field1,7,1)=" " then 'checking for 1st space
field1a = left(field,6) 'take first six characters
endif
if mid(field1, 12,1)="" then 'checking for 2nd space
field1b = right(field1,5) 'take last 5 characters
endif
field1 = field1a + field1b

this may or may not work. you'll have to do some switching around for the different scenerios you may run into, no 1st blank, yes 2nd blank, and equal field1 accordingly.

kerri

 

[tonyhomewood]

Try this function

Public Function removespaces(f As String) As String

Dim i As Integer
removespaces = ""

i = 1

While i < Len(f)
removespaces = removespaces & IIf(Mid(f, i, 1) = &quot; &quot;, &quot;&quot;, Mid(f, i, 1))
i = i + 1
Wend

End Function

 

[Guest_imported]

Try writing a custom function to replace all instances of one string with another string.

Public Function SwitchOut(TextIn As String, Fnd As String, Rplc As String) As String

Dim X as Integer

For X = 1 to Len(TextIn)
SwitchOut = SwitchOut & Iif(Mid(TextIn,X,1)=Fnd,Rplc,Mid(TextIn,X,1))
Next X

End Function

This function will run sequentially, one character at a time through whatever string you pass to the function, and if the character it is evaluating matches the Fnd argument, it replaces it with the Rplc argument, otherwise it adds the character to the string.

To call the function from a query, add an expression to your query grid called NoSpace:SwitchOut(MyField,&quot; &quot;,&quot;&quot
where MyField is the name of the field that contains your data to be adjusted.

You can also run an update query on MyField where you would update to SwitchOut(MyField,&quot; &quot;,&quot;&quot.

Good Luck

 

[mymou]

If this is a one off then just do a search and replace on the field.

Otherwise see above or

removespaces = Excel.WorksheetFunction.Substitute(f, &quot; &quot;, &quot;&quot'with excel reference

or Access 2000

removespaces = Replace(f, &quot; &quot;, &quot;&quot;, 1, -1)'dont know if this works - but looks easy enough

 

[gol4]

Great function Tony
Well written and functional (short & sweet)
It was also dropping the last character so I changed the line
While i < Len(f)
to
While i <= Len(f)

 

[ruthcali]

Wow. thanks.

i am going to try every persons suggestion and let you know how it works.

thanks so much for the support!!!!

ruth.jonkman@wcom.com

 

[MichaelRed]

MyString = &quot;This is a test for replace spaces&quot;

? Replace(MyString, &quot; &quot;, &quot;&quot
Thisisatestforreplacespaces

Oh, well just couldn't resist. I mean a &quot;one-liner&quot;? Why NOT??????????


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ruthcali]

Kerrigirrl:
thanks for your suggestion.
your way took the first and last part of the string, but not the middle. but it's a smart start.

Tony and Randy:
very nice function. i used it and it worked perfectly. you both use the same concept and it works beautifully!

Stewart and Michael:
i don't have Access2000. is there a built in function called Replace in Access2000? when i type it in Access97 i get a 'user defined function is undefined' error.

Gol4: you are right about dropping off the last character. but adding the = sign did the trick!


thank you all for your help!!!
it solved my situation.


ruth.jonkman@wcom.com