How to trim misc blanks within strings?

[geolemon]

Any way to do this as part of a data cleansing routine?

One customer has supplied text that looks like this:
" 100 UF_____________ _______16V_____ ________6555___ __________ELEC_ ___________SM__ __"
Another row:
"___0.1UF______ ________16V____ ________0603___ __________X7R__ _______________ _____"

Obviously, it would be much more desirable to store that as:
"100 UF 16V 6555 ELEC SM"
"0.1UF 16V 0603 X7R"

In Excel, the "trim" function removes all whitespace, converting it into single spaces, I believe.
In Access, it doesn't work that way... in fact, displaying the raw description and the TRIM(descriptio n), I actually can't even see a difference...

If it helps, I will be running this SQL as part of an VBA script that performs the data import. I'm pulling the data from Excel spreadsheets that have been pre-formatted.

EDIT: Pretend the underscore characters above are spaces!
In a frustratingly ironic twist of fate, THIS FORUM won't even let me post repeated spaces without truncating them down to one or two!
Not Funny. [takes 2 Advil]

[geolemon]

Please tell me something like THIS isn't the solution:

REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(RE PLACE(DBimport. description,"----", "-"),"---","-"),"--","-"),"--","-"),"--","-"),"--","-"),"--","-")

I might have gone a little overboard - but that's part of what I'm trying to exemplify- why it doesn't seem like the right fix.

And, although even this might work 99% of the time, it's pretty Rube-Goldberg and has a finite limitation (although, 384 if I did my math right, for this particular combo)...

That's NOT elegant - there must be a "right" answer... Help me if you know it!

[FishVal]

Hello, geolemon.

You could parse substrings into an array with Split() function, apply Trim() function on each array element and rejoin string with Join() function.

[Stewart Ross]

Hi Geolemon. Fish's suggestion on using the Split function is certainly one to follow up. I take a different approach to this, with the function below which returns a string where multiple occurrences of a particular character are replaced by single occurrences only. The default character replaced is the space, but any character can be specified.

Public Function fRemoveExcessChars(ByVal strInput, Optional ByVal strToReplace = " ") As String
'INPUT:  strInput - any string
'OUTPUT: a string in which all multiple occurrences of the character
'        strToReplace have been replaced by single occurrences
    Dim strChar As String, strResult As String
    Dim intI As Integer, intL As Integer
    Dim blLastCharReplaced As Boolean
    blLastCharReplaced = False
    if IsNull(strInput) then Exit Function 
    intL = Len(strInput)
    For intI = 1 To intL
        strChar = Mid(strInput, intI, 1)
        If strChar = strToReplace Then
            If Not blLastCharReplaced Then
                If (intI <> 1) And (intI <> intL) Then
                    strResult = strResult & strChar
                End If
                blLastCharReplaced = True
            End If
        Else
            blLastCharReplaced = False
            strResult = strResult & strChar
        End If
    Next intI
    fRemoveExcessChars = strResult
End Function

To use the function, place the code in any public code module (one which is visible from the Modules tab in Access) then add a calculated field to a query like this (in the access query editor):

NewText: fRemoveExcessCh ars([your field name])

Examples of use from VB Immediate Window:

? fRemoveExcessChars("10uf               20V             Tantalum Bead")
10uf 20V Tantalum Bead
? fRemoveExcessChars(" 100 UF            16V              6555             ELEC SM ")
100 UF 16V 6555 ELEC SM

I use the function to pre-process user-entered strings where on occasions multiple spaces have been entered between words.

By the way, the Trim function in VBA is in effect a combination of the LTrim and RTrim functions - it removes any excess spaces from the left and right of a string. It does not remove multiple spaces within a string, unlike the Excel Trim function (to which my example above is equivalent).

-Stewart

[geolemon]

Fish -

I'd have to do it row-by-row, stepping through the table, more VBA than SQL...
One description might have 2 words, another 12, with unpredictable amount of space in between. It's an option, just a bit more complicated than I had hoped for - I've really got to write an entire program around that.

Stewart -
And just as I say the above, you seem to have done all the work for me, lol.
Nice! And thanks!

I suppose I was hoping for a SQL function I could execute in a single-query-stroke... since I do wish to apply it to the entire table, rather than the entire column, but row-by-row.

However, the fact that you've gone through this already should probably tell me that I'm not the only one with this need, and there is NOT a simple, standard, best-practice function inside of SQL that would allow for this!

I wonder in that case if I can actulaly use the Excel "trim" function, invoked programatically , just prior to actually executing the VBA script that performs the TransferSpreads heet function to import that data?

[Stewart Ross]

If you are using Excel as an automation server from code running in Access you can use the WorksheetFuncti on method of the Application object to access the Trim function, possibly using code along these lines:

Dim objExcel as New Excel.Application
 
<... code to open workbook etc ...>
 
For i = 1 to lastrow
    with objExcel.Activesheet.Cells(i, 1)
      .value = objExcel.WorksheetFunction.Trim(.value)
    End With
next i

-Stewart