Prev: Run-time error 3061; Too few parameters. Expected 2
Next: Using the DateDiff function based on 5 days
From: rlj on 27 Apr 2010 17:11 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 27 Apr 2010 17:30 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 27 Apr 2010 17:38 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 27 Apr 2010 18:19 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 29 Apr 2010 00:28
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. |