Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Removing <CR> and <LF> from a field

    I needed to do this so that auto adjusting the column width would work right. As I couldn't find a built in function to do it, I wrote this. You are welcome to use it, improve it etc.

    If there is a built in function, I'd be delighted to hear about it.

    Alan
    Code:
    Function removeCR(inputdata)
        outputdata = ""
        For i = 1 To Len(inputdata)
            If Mid(inputdata, i, 1) <> Chr(13) And Mid(inputdata, i, 1) <> Chr(10) Then
                outputdata = outputdata & Mid(inputdata, i, 1)
            End If
        Next i
        removeCR = outputdata
        
    End Function

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by alan sh View Post
    I needed to do this so that auto adjusting the column width would work right. As I couldn't find a built in function to do it, I wrote this. You are welcome to use it, improve it etc.

    If there is a built in function, I'd be delighted to hear about it.

    Alan
    Code:
    Function removeCR(inputdata)
        outputdata = ""
        For i = 1 To Len(inputdata)
            If Mid(inputdata, i, 1) <> Chr(13) And Mid(inputdata, i, 1) <> Chr(10) Then
                outputdata = outputdata & Mid(inputdata, i, 1)
            End If
        Next i
        removeCR = outputdata
        
    End Function
    Alan, just a thought - does the CLEAN function do what you need? (It's supposed to remove ASCII characters 0 to 31 from strings.)

  3. The Following User Says Thank You to jeremybarker For This Useful Post:

    alan sh (2014-03-07)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The Clean function removes all the "non-printable characters". These are the 7 bit ascii 0-31 (which includes the CR and LF) that you mention and I believe also 8-bit unicode characters 127, 129, 141, 143, 144, and 157.

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    alan sh (2014-03-07)

  6. #4
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks Steve & Jeremy, I didn't find that one.
    The help says:

    The Clean function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the Clean function does not remove these additional nonprinting characters.

    Alan
    Last edited by alan sh; 2014-03-07 at 07:55.

  7. The Following User Says Thank You to alan sh For This Useful Post:

    sdckapr (2014-03-07)

  8. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Thank you for that clarification.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •