I have a string that I want to write to Excel
Example of the string is:
Dim excelString As String = "this is my string <br /> this is my second string"
I want to write that string to Excel but with line breaks, so the result will be
How can i achieve this? I tried replacing <br /> with vbLf but Excel just process it as a string. I can't manually alter the Excelfile as this is an automated process.
You could try using the NewLine property (documentation):
Dim excelString As String = "this is my string <br /> this is my second string".Replace("<br />", Environment.NewLine)
My guess as to why the vbLf constant is failing (documentation) is because it represents just the line feed and Excel is expecting the carriage-return and line feed.
I splitted the string on <br /> and added it to a stringbuilder.
Private Function Split(tosplit As String)
Dim myDelims As String() = New String() {"<br />"}
Dim splitted As List(Of String) = tosplit.Split(myDelims, StringSplitOptions.None).ToList()
Dim stb As New Text.StringBuilder()
For Each item In splitted
If Not item.Equals("") Then
stb.AppendLine(item.Trim())
End If
Next
Dim returnValue As String = stb.ToString().Trim()
Return returnValue
End Function
Apperiantly when you add items to a stringbruilder it puts linebreaks that Excel understands in the string which are preserved as you convert the stringbuilder back to a string
Environment.NewLine under the hood. I think my code and your code does the same thing in practice, only mine is more concise.