Char(10)

[Obied70]

Hi Geniuses,
I need some help inserting line breaks where a cell contains a certain word. For example the address line "100 East King Way Suite 100" needs to have a line break so that "Suite 100" is on another line in the same cell. I would do it manually but I have ~40,000 rows to go through so using a formula/vba is a much better option. Thanks for your help!

 

[Obied70]

Re: Char(10) Help

VBA is fine here too if theres no simple cell formula to use

 

[FormR]

Re: Char(10) Help

Hi, what is the rule for where CRLF needs inserting? Is it always the second to last space?

 

[RoryA]

Re: Char(10) Help

If it's always "Suite", you can just use Find/Replace:
-Select the data
-Ctrl+H
-Enter Suite in the Find box
-Click in the replace box, press Ctrl+J and then type Suite
-Press Replace all

 

[Obied70]

Re: Char(10) Help

It's going to be a variation of "suite" with varying number of characters after. So I need, every time the word "suite", "ste", etc is found, to place "suite" and everything after that on the next line in the same cell

 

[Joe4]

Re: Char(10) Help

I found VBA code here that removes those soft carriage returns: https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/
We can reverse it to add them in before every incidence of the word "Suite", like this:

Sub AddCarriageReturns()
    Dim MyRange As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    For Each MyRange In ActiveSheet.UsedRange
        If InStr(MyRange, "Suite") > 0 Then
            MyRange = Replace(MyRange, "Suite", Chr(10) & "Suite")
        End If
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Scott T]

Re: Char(10) Help

Maybe, where the address is in A1

=TRIM(IF(ISNUMBER(SEARCH("suite",A1)),LEFT(A1,SEARCH(" Suite",A1)-1)&CHAR(10)&MID(A1,SEARCH("suite",A1),999),A1))

 

[Joe4]

Re: Char(10) Help

If different cases is an issue (i.e. Suite, suite, SUITE), then you can use this version of VBA code.

Sub AddCarriageReturns()
    Dim MyRange As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    For Each MyRange In ActiveSheet.UsedRange
        If InStr(UCase(MyRange), "SUITE") > 0 Then
            MyRange = Replace(UCase(MyRange), "SUITE", Chr(10) & "Suite")
        End If
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Obied70]

Re: Char(10) Help

You guys never fail to blow my tiny little brain to bits. I'll give this a whirl and report back

 

[Obied70]

Re: Char(10) Help

I'm getting a type mismatch and so the macro is not running through the entirety of the sheet.