How can I remove line breaks from a cell but keep the address format in the same cell VBA
The cell I'm trying to remove line breaks from is this:
(Blank line break) Test Company 1640 Test Avenue, New York, NY 10035 (000)123-456 (Blank line break)
I want to remove the two rows that have line breaks. Ive tried to use find and replace to replace the line breaks with alt code 0010 but it creates a long string.
I want to end up with this:
Test Company 1640 Test Avenue, New York, NY 10035 (000)123-456
Any help would be appreciated.
Something like this
Dim v v = Trim(cell.Value) if left(v,1)=vblf then v = right(v, len(v)-1) 'remove leading break if right(v,1)=vblf then v = left(v, len(v)-1) 'remove trailingbreak v = replace(v, vbLf & vbLf, vbLf) 'remove blank lines cell.value=v
Here is a UDF that works similar to the worksheet TRIM function, but allows you to specify the character to trim:
Option Explicit Function trimCHAR(ByVal S As String, char As String) Dim RE As Object Dim I As Long Set RE = CreateObject("vbscript.regexp") With RE .Global = True .MultiLine = True 'need to do separately, otherwise multiple chars within will 'be removed .Pattern = char & "$" S = .Replace(S, "") 'Remove extra chars within or at end of string .Pattern = "^" & char S = .Replace(S, "") 'Remove extra chars at start of string End With trimCHAR = S End Function
In your situation, you could use it like:
OR you could use it as part of a macro that iterates through all the cells to be processed.
The idea for this came from Power Query's
Text.Trimfunction which only works on the beginning and end of the string, but allows any character string to be specified; along with an adaptation of that by Ken Puls, which adds the worksheet function TRIM feature of removing sequential "chars" within the string with only a single one.