Access VBA remove CR & LF only from the beginning of a text string by searching for them

Question

Hi there

I need to remove line breaks from the beginning of a memo type records. I dont want to use the replace function as it would remove all line breaks from the record which is not desired. Its only the line breaks at the beginning of the field that I am interested in removing.

Furthermore, the my records do not always begin with a line break so I cant really use text positioning, the solution would be to look for line break at the beginning instead of always expecting it at the beginning.

click here for a sample of what my data looks like

any help will be greatly appreciated

Answer 1

Private Sub TestLineFeed()
Dim strString$, strTestChar, booStartsWith_CR As Boolean

strString = Chr$(13) & "some text"

strTestChar = "2"
'strTestChar = Chr$(13)   ''This is a CR.  

booStartsWith_CR = (Left(strString, 1) = strTestChar)

Debug.Print "-----"
Debug.Print "Raw: " & strString
Debug.Print booStartsWith_CR

If booStartsWith_CR Then
    strString = Mid(strString, 2, 100)
End If

Debug.Print "-----"
Debug.Print "New: " & strString
End Sub

Note alternatives for strTestChar so you can see the action. You should notice "-----" in your Immediate Window is followed by a CR, thus a blank line; and this can be removed. Mid(strString, 2, 100) will need some tweaking, but the idea is to copy over your memo string without the first character.

Answer 2

Replace does not replace all occurences when you use the count argument: http://office.microsoft.com/en-us/access/HA012288981033.aspx

You can test it like so:

s1 = vbCrLf & "abc"
s2 = "ab" & vbCrLf & "c"

MsgBox "---" & IIf(Left(s1, 2) = vbCrLf, Replace(s1, vbCrLf, "", , 1), s1)
MsgBox "---" & IIf(Left(s2, 2) = vbCrLf, Replace(s2, vbCrLf, "", , 1), s2)

Answer 3

I would use a function like this. It's fairly straight-forward and easily adapted to other circumstances. For example, to remove leading spaces too, add another test to the if (c = vbCr) line.

Function LTrimCRLF(s As String) As String
  Dim index As Integer, start As Integer, strLen As Integer
  Dim c As String

  strLen = Len(s)
  index = 1
  start = -1

  Do While (index <= strLen) And (start = -1)
    c = Mid(s, index, 1)

    If (c = vbCr) Or (c = vbLf) Then
      index = index + 1
    Else
      start = index
    End If
  Loop

  If start = -1 Then
    LTrimCRLF = ""
  Else
    LTrimCRLF = Mid(s, start)
  End If
End Function

Here's a test routine:

Sub TestLTrimCRLF()
  Dim withWS As String, noWS As String, blank As String, onlyWS As String

  withWS = vbCrLf & "  this string has leading white space"
  noWS = "this string has no leading white space"
  onlyWS = vbCrLf & " " & vbCrLf & " "
  blank = ""

  Say "with WS: {" & LTrimCRLF(withWS) & "}"
  Say "no WS:   {" & LTrimCRLF(noWS) & "}"
  Say "only WS: {" & LTrimCRLF(onlyWS) & "}"
  Say "blank:   {" & LTrimCRLF(blank) & "}"
End Sub

BTW, I tried looking at your sample data, but it says the document is not available. Maybe you need to make it public or something?