From: rlj on
I have several item numbers that have various spaces
1234 5678 A
and I want to strip the spaces and replace with dashes
1234-5678-A


From: Song on
On Apr 27, 2:11 pm, rlj <r...(a)discussions.microsoft.com> wrote:
> I have several item numbers that have various spaces
> 1234    5678  A
> and I want to strip the spaces and replace with dashes
> 1234-5678-A

Open table in datasheet view, highlight the column. Ctrl+H to bring
replace dialog box. Put space in find box and put - in replace with.
select partial instead of whole field.
From: John W. Vinson on
On Tue, 27 Apr 2010 14:11:02 -0700, rlj <rlj(a)discussions.microsoft.com> wrote:

>I have several item numbers that have various spaces
>1234 5678 A
>and I want to strip the spaces and replace with dashes
>1234-5678-A
>

I'd do this in two stages: one to replace all the strings of multiple blanks
with a single blank:

Update Tablename
SET ItemNumber = Replace([ItemNumber], " ", " ")
WHERE ItemNumber LIKE "* *";

This finds all instances where there are two consecutive blanks and replaces
them by one blank. Run it two or three times until no such records are left.

Then replace the blanks:

UPDATE Tablename
SET ItemNumber = Replace([ItemNumber], " ", "-")
WHERE ItemNumber LIKE "* *";

--

John W. Vinson [MVP]
From: Wayne-I-M on
Hi

MAKE A BACKUP OF YOUR DATABASE 1ST

next, create a new module

Public Function RemoveLotsOfSpaces(ByRef data As String) As String
Dim arr
Dim i As Long
arr = Split(data, " ")
For i = LBound(arr) To UBound(arr)
If arr(i) <> "" Then
arr(i) = arr(i) & "-"
End If
Next i
RemoveLotsOfSpaces = Join(arr, "")
End Function

Next create a new query (of course change the names to what they really are)
I assume you have a FieldName and a NewFieldName in your table - you want to
put the contents of FieldName into NewFieldsName with the spaces replaced by -

UPDATE tablename SET tablename.newfieldname =
RemoveLotsOfSpaces([tablename]![fieldname]);


Hope this helps




--
Wayne
Manchester, England.



"rlj" wrote:

> I have several item numbers that have various spaces
> 1234 5678 A
> and I want to strip the spaces and replace with dashes
> 1234-5678-A
>
>
From: James A. Fortune on
On Apr 27, 5:11 pm, rlj <r...(a)discussions.microsoft.com> wrote:
> I have several item numbers that have various spaces
> 1234    5678  A
> and I want to strip the spaces and replace with dashes
> 1234-5678-A

Here's a brute force method:

Public Function OneOrMoreBlanksToHyphen(varIn As Variant) As Variant
Dim strTemp As String
Dim intFirstBlank As Integer
Dim strOut As String
Dim I As Integer
Dim intBlanksSkipped As Integer

OneOrMoreBlanksToHyphen = varIn
If IsNull(varIn) Then Exit Function
strTemp = Trim(CStr(varIn)) 'Trimmed
If Len(strTemp) = 0 Then Exit Function
intFirstBlank = InStr(1, strTemp, " ", vbTextCompare)
If intFirstBlank = 0 Then Exit Function
strTemp = CStr(varIn) 'Untrimmed
I = 1
Do While I <= Len(strTemp)
'Skip initial blanks, but add to output string
Do While MID(strTemp, I, 1) = " " And I <= Len(strTemp)
strOut = strOut & MID(strTemp, I, 1)
I = I + 1
Loop
Do While I <= Len(strTemp)
'Skip nonblanks, but add to output string
Do While MID(strTemp, I, 1) <> " " And I <= Len(strTemp)
strOut = strOut & MID(strTemp, I, 1)
I = I + 1
Loop
'At a blank
'Skip blank and following blanks
intBlanksSkipped = 0
Do While MID(strTemp, I, 1) = " " And I <= Len(strTemp)
I = I + 1
intBlanksSkipped = intBlanksSkipped + 1
Loop
'Only replace the set of blanks if they are not at the end
If I < Len(strTemp) + 1 Then
strOut = strOut & "-"
Else
strOut = strOut & String(intBlanksSkipped, " ")
End If
Loop
Loop
OneOrMoreBlanksToHyphen = strOut
End Function

Note: I only did superficial testing, but it seems to do what you
require.

James A. Fortune
MPAPoster(a)FortuneJames.com

Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular
procedure, but I am under no obligation to modify these examples to
provide added functionality or to construct procedures to meet your
specific requirements. Any code samples posted contain no known
hidden material defects. However, anyone who uses any code sample
posted does so with the understanding that they are responsible for
any testing of any illustrative code sample for any particular use.
Furthermore, anyone using an illustrative code sample I provide or
code derived from it does so at their own risk.