通过搜索访问 VBA，仅从文本字符串的开头删除 CR 和 LF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2533370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access VBA remove CR & LF only from the beginning of a text string by searching for them
提问by uZI
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.
此外，我的记录并不总是以换行符开头，所以我不能真正使用文本定位，解决方案是在开头寻找换行符，而不是总是在开头期待它。
回答by SBinVA
If Len(string) > 0 Then
Do While Left(string,1)= chr(13) Or Left(string,1)= chr(10) or Left(string,1) = " "
string = Right(string, len(string)-1)
Loop
End If
This will check to make sure the string isn't empty, then runs a simple loop to remove the left-most character as long as it is either a CR (chr(13)), LF (chr(10)), or a space (" ").
这将检查以确保字符串不为空，然后运行一个简单的循环来删除最左边的字符，只要它是 CR (chr(13))、LF (chr(10)) 或空间 （” ”）。
Once the loop hits the first character that doesn't match the criteria, it stops and you have the desired result of trimming all extra CR, LF, and space characters only from the beginning of the string.
一旦循环遇到与条件不匹配的第一个字符，它就会停止，您将获得所需的结果，即仅从字符串的开头修剪所有额外的 CR、LF 和空格字符。
Since it's relatively short, I just put it in the event procedure where needed, you could also modify it to be a public function in a module if you see fit.
由于它相对较短，我只是将它放在需要的事件过程中，如果您认为合适，您也可以将其修改为模块中的公共函数。
回答by Fionnuala
Replace does not replace all occurences when you use the count argument: http://office.microsoft.com/en-us/access/HA012288981033.aspx
当您使用计数参数时，替换不会替换所有出现：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)
回答by Magnus
Improving upon what SBinVA wrote
改进 SBinVA 所写的内容
The following code does not need the if statement and it is easy to expand to more character (space, tabs, etc.).
下面的代码不需要 if 语句，很容易扩展到更多字符（空格、制表符等）。
(It also assumes line breaks can originate from a file that can comes from other systems, so vbCr and vbLf are used separately, which takes care of all scenarios.)
（它还假设换行符可以源自一个可以来自其他系统的文件，因此 vbCr 和 vbLf 是分开使用的，这可以处理所有情况。）
Public Function trimCrOrLf(ByVal s As String) As String
Dim firstChar As String
firstChar = Left(s, 1)
Do While InStr(vbCr & vbLf, firstChar) > 0
s = Mid(s, 2)
firstChar = Left(s, 1)
Loop
trimCrOrLf = s
End Function
回答by HansUp
Consider a SQL UPDATE statement to discard only those CRLF at the beginning of each memo field.
考虑一个 SQL UPDATE 语句只丢弃每个备注字段开头的那些 CRLF。
UPDATE MyTable SET MyTable.memo_field = Mid([memo_field],3)
WHERE (((MyTable.memo_field) Like Chr(13) & Chr(10) & "*"));
回答by Whome
My contribution to VBA trimwhitespace() function, loop finds for first non-whitespace index, splits a string, then same thing for trailing whitespaces. Left+Right functions are run only once. If you need just leftTrim or rightTrim it's easy to introduce new arguments or separate functions.
我对 VBA trimwhitespace() 函数的贡献，循环查找第一个非空白索引，拆分字符串，然后对尾随空白进行同样的操作。Left+Right 函数只运行一次。如果您只需要 leftTrim 或 rightTrim，则很容易引入新参数或单独的函数。
Function trimWhitespace(str As String) As String
Dim idx As Long
Dim ch As String
' LeftTrim
If Len(str) > 0 Then
idx = 1
ch = Mid(str, idx, 1)
Do While ch = Chr(13) Or ch = Chr(10) Or ch = " "
idx = idx + 1
ch = Mid(str, idx, 1)
Loop
If (idx > 1) Then str = Right(str, Len(str) - idx)
End If
' RightTrim
idx = Len(str)
If idx > 0 Then
ch = Mid(str, idx, 1)
Do While ch = Chr(13) Or ch = Chr(10) Or ch = " "
idx = idx - 1
ch = Mid(str, idx, 1)
Loop
If (idx < Len(str)) Then str = Left(str, idx)
End If
trimWhitespace = str
End Function
回答by Ben
This will trim all leading andtrailing spaces, carriage returns, tabs, and other non-printable characters.
这将修剪所有前导和尾随空格、回车、制表符和其他不可打印的字符。
Public Function TrimSpecial(InputString As Variant) As String
' This will trim leading/trailing spaces and non-printable characters from the passed string.
Dim i As Integer
Dim str As String
On Error GoTo ErrorHandler
str = InputString
For i = 1 To Len(str)
If Asc(Mid(str, i, 1)) > 32 And Asc(Mid(str, i, 1)) < 127 Then
' Valid character found. Truncate leading characters before this.
str = Mid(str, i)
Exit For
End If
Next i
For i = Len(str) To 1 Step -1
If Asc(Mid(str, i, 1)) > 32 And Asc(Mid(str, i, 1)) < 127 Then
' Valid character found. Truncate trailing characters after this.
str = Mid(str, 1, i)
Exit For
End If
Next i
TrimSpecial = str
Exit_Function:
Exit Function
ErrorHandler:
MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure TrimSpecial"
GoTo Exit_Function
Resume Next
Resume
End Function
You can use this routine to test it:
您可以使用此例程来测试它：
Public Sub Test_TrimSpecial()
' Run this to test the TrimSpecial function.
Dim x As String
x = vbCrLf & " " & vbTab & " ab cd" & vbCrLf & vbTab & " xyz " & vbCr & vbCrLf
Debug.Print "-----"
Debug.Print ">" & x & "<"
Debug.Print "-----"
Debug.Print ">" & TrimSpecial(x) & "<"
Debug.Print "-----"
End Sub
回答by Smandoli
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.
请注意 strTestChar 的替代方案，以便您可以看到操作。你应该注意到在你的立即窗口中的“-----”后面跟着一个 CR，因此是一个空行；这可以删除。Mid(strString, 2, 100) 需要一些调整，但想法是复制没有第一个字符的备忘录字符串。
回答by Todd
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.
我会使用这样的功能。它相当简单，很容易适应其他情况。例如，要删除前导空格，请向该if (c = vbCr)行添加另一个测试。
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?
顺便说一句，我尝试查看您的示例数据，但它说该文档不可用。也许你需要把它公之于众？
