My strings might have multiple newline characters at the
beginning of the string AND/OR at the end of the string.
What is a good way to remove those newline or
carriage return characters?

BTW, I am only concerned about extra newlines at the beginning
or end of the string. It is okay to have newline characters
in the middle of the string.

~Robert

Hi Robert,

Am Wed, 18 Mar 2015 23:47:41 -0700 schrieb Robert Crandal:

BTW, I am only concerned about extra newlines at the beginning
or end of the string. It is okay to have newline characters
in the middle of the string.

try:

Sub Test()
Dim myStr As String

myStr = Range("A1")

With Application
If InStr(.Trim(myStr), Chr(10)) = 1 Then
Do
myStr = Right(.Trim(myStr), Len(myStr) - 1)
Loop While InStr(.Trim(myStr), Chr(10)) = 1
End If

If InStrRev(.Trim(myStr), Chr(10)) = Len(.Trim(myStr)) Then
Do
myStr = .Trim(Left(.Trim(myStr), Len(.Trim(myStr)) - 1))
Loop While InStrRev(.Trim(myStr), Chr(10)) = Len(.Trim(myStr))
End If
End With
Range("A1") = myStr
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

One way...

Sub TrimNewlineChars()
Dim rng, vData, lEnd&
For Each rng In Selection
vData = Split(rng.Value, vbLf): lEnd = UBound(vData)
If vData(0) = "" Then vData(0) = "~"
If vData(lEnd) = "" Then vData(lEnd) = "~"
rng.Value = Join(Filter(vData, "~", False), vbLf)
Next 'rng
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"Claus Busch" wrote

Sub Test()
Dim myStr As String

myStr = Range("A1")

With Application
If InStr(.Trim(myStr), Chr(10)) = 1 Then
Do
myStr = Right(.Trim(myStr), Len(myStr) - 1)
Loop While InStr(.Trim(myStr), Chr(10)) = 1
End If

If InStrRev(.Trim(myStr), Chr(10)) = Len(.Trim(myStr)) Then
Do
myStr = .Trim(Left(.Trim(myStr), Len(.Trim(myStr)) - 1))
Loop While InStrRev(.Trim(myStr), Chr(10)) = Len(.Trim(myStr))
End If
End With
Range("A1") = myStr
End Sub


Hi Claus.....

I'm actually reading data from a text file and storing it in
a string variable. I'm not pulling strings out of the spreadsheet
or from Range("A1"). I dunno if that matters or not.

I tried your code on a string that had 2 carriage returns at the
beginning of the string and 2 carriage returns at the end. The
above code only removed 1 carriage return from the end of
my string.

Does your code only work if my string is stored in a cell
such as "A1"??

better self-documented...

Sub TrimNewlineChars()
Dim rng, vData, lEnd&
Const sFilterFlag$ = "~"

For Each rng In Selection
vData = Split(rng.Value, vbLf): lEnd = UBound(vData)

'Flag leading/trailing linefeeds
If vData(0) = Empty Then vData(0) = sFilterFlag
If vData(lEnd) = Empty Then vData(lEnd) = sFilterFlag

'Return value with leading/trailing linefeeds removed
rng.Value = Join(Filter(vData, sFilterFlag, False), vbLf)
Next 'rng
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

Sub TrimNewlineChars()
Dim rng, vData, lEnd&
For Each rng In Selection
vData = Split(rng.Value, vbLf): lEnd = UBound(vData)
If vData(0) = "" Then vData(0) = "~"
If vData(lEnd) = "" Then vData(lEnd) = "~"
rng.Value = Join(Filter(vData, "~", False), vbLf)
Next 'rng
End Sub


Hey Gary...what if I have the following code:

Dim myStr as String

myStr = ReadTextFile("C:\inp.txt")

I now want to remove any carriage returns at the
beginning and/or end of the string in myStr. How
do I use your function in this context?

"Claus Busch" wrote

Sub Test()
Dim myStr As String

myStr = Range("A1")

With Application
If InStr(.Trim(myStr), Chr(10)) = 1 Then
Do
myStr = Right(.Trim(myStr), Len(myStr) - 1)
Loop While InStr(.Trim(myStr), Chr(10)) = 1
End If

If InStrRev(.Trim(myStr), Chr(10)) = Len(.Trim(myStr)) Then
Do
myStr = .Trim(Left(.Trim(myStr), Len(.Trim(myStr)) - 1))
Loop While InStrRev(.Trim(myStr), Chr(10)) =
Len(.Trim(myStr))
End If
End With
Range("A1") = myStr
End Sub


Hi Claus.....

I'm actually reading data from a text file and storing it in
a string variable. I'm not pulling strings out of the spreadsheet
or from Range("A1"). I dunno if that matters or not.

I tried your code on a string that had 2 carriage returns at the
beginning of the string and 2 carriage returns at the end. The
above code only removed 1 carriage return from the end of
my string.

Does your code only work if my string is stored in a cell
such as "A1"??

Geez.., that's nice to know! Coulda'/shoulda' said so in the 1st
place!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

Geez.., that's nice to know! Coulda'/shoulda' said so in the 1st place!<g


Hi again Gary! 8)

The truth is, I didn't think it mattered to much how the string
was stored. I altered Claus' code so that the string came
from a different source, and not from cell A1. I thought it would
work regardless, but I guess I was wrong. my mistake then.

No problem...

Sub TrimNewlineChars2()
Dim sFile$, vData, n&
Const sFilterFlag$ = "~"

'Prompt for the file
sFile = Application.GetOpenFilename
If sFile = Empty Then Exit Sub
vData = Split(ReadTextFile(sFile), vbCrLf)

'Flag leading/trailing linefeeds
For n = LBound(vData) To UBound(vData)
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n
For n = UBound(vData) To LBound(vData) Step -1
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n

'Return value with leading/trailing linefeeds
Selection.Value = Join(Filter(vData, sFilterFlag, False), vbLf)
End Sub

...where I suspect you already have my following function...


Function ReadTextFile$(Filename$)
' Reads large amounts of data from a text file in one single step.
Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile(): Open Filename For Input As #iNum
ReadTextFile = Space$(LOF(iNum))
ReadTextFile = Input(LOF(iNum), iNum)

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFile()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Just so you understand.., when CrLf exists in a text file loaded into a
string variable and dumped into a cell, Excel replaces CrLf with Lf
only because that's the newline character (Alt+Enter) used in cells.
Same holds true for text stored in a text control (ie: textbox).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops.., I forgot you're not using my Get_FileToOpen routine which
returns an empty string. Replace the appropriate line with...

If sFile = False Then Exit Sub

OR

sFile = Get_FileToOpen

...and use the following functions in place of doing the extra typing in
routines...

Function Get_FileToOpen$(Optional FileTypes$ = "All Files ""*.*"",
(*.*)")
Dim vFile
vFile = Application.GetOpenFilename(FileTypes)
Get_FileToOpen = IIf(vFile = False, "", vFile)
End Function

Function Get_FileToSave$(Optional FileOut$)
Dim vFile
vFile = Application.GetSaveAsFilename(FileOut)
Get_FileToSave = IIf(vFile = False, "", vFile)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

'Return value with leading/trailing linefeeds
Selection.Value = Join(Filter(vData, sFilterFlag, False), vbLf)
End Sub


Great, that's what I was looking for.

But, since I was sending the output to a new string variable, and
not an actual spreadsheet cell, I modified the "vbLf" above to
a "vbCrLf" and got the results I needed.

Thank you Gary, and Claus, for always being such helpful
experts. It is much appreciated.

"GS" wrote:

'Return value with leading/trailing linefeeds
Selection.Value = Join(Filter(vData, sFilterFlag, False), vbLf)
End Sub


Great, that's what I was looking for.

But, since I was sending the output to a new string variable, and
not an actual spreadsheet cell, I modified the "vbLf" above to
a "vbCrLf" and got the results I needed.

Thank you Gary, and Claus, for always being such helpful
experts. It is much appreciated.

You're always welcome...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

'Return value with leading/trailing linefeeds
Selection.Value = Join(Filter(vData, sFilterFlag, False), vbLf)
End Sub


Great, that's what I was looking for.

But, since I was sending the output to a new string variable, and
not an actual spreadsheet cell, I modified the "vbLf" above to
a "vbCrLf" and got the results I needed.

Thank you Gary, and Claus, for always being such helpful
experts. It is much appreciated.

Actually, you can leave that vbCrLf there because even if it was being
placed in a cell, Excel would remove the Cr anyway!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Revised edition...

Sub TrimNewlineChars2()
Dim sFile$, vData, n&
Const sFilterFlag$ = "~"

'Prompt for the file
sFile = Get_FileToOpen
If sFile = Empty Then Exit Sub
vData = Split(ReadTextFile(sFile), vbCrLf)

'Flag leading/trailing linefeeds
For n = LBound(vData) To UBound(vData)
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n
For n = UBound(vData) To LBound(vData) Step -1
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n

'Return value with leading/trailing linefeeds
Selection.Value = Join(Filter(vData, sFilterFlag, False), vbCrLf)
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

OR...

sTextString = TrimNewlineChars3


Function TrimNewlineChars3$()
Dim sFile$, vData, n&
Const sFilterFlag$ = "~"

'Prompt for the file
sFile = Get_FileToOpen
If sFile = Empty Then Exit Function
vData = Split(ReadTextFile(sFile), vbCrLf)

'Flag leading/trailing linefeeds
For n = LBound(vData) To UBound(vData)
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n
For n = UBound(vData) To LBound(vData) Step -1
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n

'Return value with leading/trailing linefeeds
TrimNewlineChars3 = Join(Filter(vData, sFilterFlag, False), vbCrLf)
End Function 'TrimNewlineChars3

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops...

Function TrimNewlineChars3$()
Dim sFile$, vData, n&
Const sFilterFlag$ = "~"

'Prompt for the file
sFile = Get_FileToOpen
If sFile = Empty Then Exit Function
vData = Split(ReadTextFile(sFile), vbCrLf)

'Flag leading/trailing linefeeds
For n = LBound(vData) To UBound(vData)
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n
For n = UBound(vData) To LBound(vData) Step -1
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n

'Return value with leading/trailing linefeeds removed
TrimNewlineChars3 = Join(Filter(vData, sFilterFlag, False), vbCrLf)
End Function 'TrimNewlineChars3

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I have a few dumb questions. Keep in mind, im
not really a VBA expert. So here goes....

"GS" wrote:

Sub TrimNewlineChars2()
Dim sFile$, vData, n&
Const sFilterFlag$ = "~"

'Prompt for the file
sFile = Application.GetOpenFilename
If sFile = Empty Then Exit Sub
vData = Split(ReadTextFile(sFile), vbCrLf)


At this point in the code, the variable "vData"
is a variant type or array, is that right?
But....the variable vData(1) is of type string,
is that right?

I'm wondering, at this point in the above code,
is it possible to run the following code?

' Get vData(1) string
myStr = vData(1)

' Put string back in vData, but preserve
' the original variant or array type? Possible?
vData = myStr

' Resume rest of code below




'Flag leading/trailing linefeeds
For n = LBound(vData) To UBound(vData)
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n
For n = UBound(vData) To LBound(vData) Step -1
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n

'Return value with leading/trailing linefeeds
Selection.Value = Join(Filter(vData, sFilterFlag, False), vbLf)
End Sub

I have a few dumb questions. Keep in mind, im
not really a VBA expert. So here goes....

"GS" wrote:

Sub TrimNewlineChars2()
Dim sFile$, vData, n&
Const sFilterFlag$ = "~"

'Prompt for the file
sFile = Application.GetOpenFilename
If sFile = Empty Then Exit Sub
vData = Split(ReadTextFile(sFile), vbCrLf)


At this point in the code, the variable "vData"
is a variant type or array, is that right?

ReadTextFile returns a string value. The Split() function returns a
zero-based array. vData is not 'typed' and so by default is type
Variant, meaning it can hold various value type[s]. Assigning the
result of Split() makes vData an array because its type is Variant.
IOW, vData was not declared as an array like so...

Dim vData()

...whereby the parenthesis would def it as an empty (no dims) array.

But....the variable vData(1) is of type string,
is that right?

In this case yes because the entire array contains text from a text
file.

vData(1) is a single element in the vData array. Each element's
position is referred to as its array index.

I'm wondering, at this point in the above code,
is it possible to run the following code?

' Get vData(1) string
myStr = vData(1)

' Put string back in vData, but preserve
' the original variant or array type? Possible?
vData = myStr

Your code sample replaces the array with a string. To preserve the
structure of the array you must ref the element by index...

myStr = vData(1)
'edit myStr as desired
vData(1) = myStr

...exactly as done in the code below to place the FilterFlag in empty
elements so the Filter() function can remove those elements and return
a resized array consisting of UBound(vData) minus (#elements with
FilterFlags).

' Resume rest of code below




'Flag leading/trailing linefeeds
For n = LBound(vData) To UBound(vData)
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n
For n = UBound(vData) To LBound(vData) Step -1
If vData(n) = Empty Then vData(n) = sFilterFlag _
Else Exit For
Next 'n

The following line should read as...

'Return value with leading/trailing linefeeds *removed*

The following line could read as...

Selection.Value = Join(Filter(vData, sFilterFlag, False), vbCrLf)

...because Excel with auto-magically remove the Cr. Lf on its own is not
desireable for a multi-line output to a text file/control. There are
cases, though, where Excel does not auto-magicically remove Cr. For
example, multi-line contents of a textbox being used in a header must
have the Cr removed beforehand.
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion