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!

 

[Joe4]

Re: Char(10) Help

It may be running against some data that we actually don't want it to hit.
Is there a defined column or range that we can limit this to?
For example, do all these entries only appear in one column?

 

[Obied70]

Re: Char(10) Help

yes sorry, column N

 

[Joe4]

Re: Char(10) Help

OK. Try this variation. I limited it down to column N, and if it finds an error, it should spit out a message telling which row is causing the error:

Sub AddCarriageReturns()

    Dim lastRow As Long
    Dim cell As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
'   Find last row in column N
    lastRow = Cells(Rows.Count, "N").End(xlUp).Row
    
    On Error GoTo err_chk
'   Loop through all cells in column N
    For Each cell In Range("N1:N" & lastRow)
'       Insert carriage return if the word "Suite" is found (any case)
        If InStr(UCase(cell), "SUITE") > 0 Then
            cell = Replace(UCase(cell), "SUITE", Chr(10) & "Suite")
        End If
    Next
    On Error GoTo 0
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    Exit Sub
    
'   Return value and cell address of cell causing error
err_chk:
    MsgBox "Error with value of: " & cell & vbCrLf & _
        "In cell: " & cell.Address
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

 

[Obied70]

Re: Char(10) Help

PERFECT, thank you so much. This saved countless double-clicks and alt+enter's.

 

[Joe4]

Re: Char(10) Help

You are welcome. Glad I could help!

 

[Obied70]

Re: Char(10) Help

Hey all looking for an update to Joes code. Is there a way to search with wildcards to find any instance of "*th"? Basically any time an instance of 5th street or 7th street or 2nd street shows up, do the same thing as Joes code with "suite." Thanks for any extra help!

 

[Obied70]

Re: Char(10) Help

Anyone? A number of searches seem to indicate that this likely is not possible but maybe theres another solution? I might be able to find/replace them and then reverse it after running Joe's macro?

 

[Joe4]

Re: Char(10) Help

Hey all looking for an update to Joes code. Is there a way to search with wildcards to find any instance of "*th"? Basically any time an instance of 5th street or 7th street or 2nd street shows up, do the same thing as Joes code with "suite." Thanks for any extra help!

I imagine that would be a bit more complex, as I imagine that you do not want to add the Char(10) before the "th", but before the whole word ending in "th". And then it sounds like there are a bunch of others you want to add to, like "nd", maybe "st", etc.

Note that you may also have some unintended consequences here, like picking up other words that end in "th", like "path" for example. Unless you can get very specific, wildcards often pick up a lot more than you bargain for.

 

[Obied70]

Re: Char(10) Help

I agree that i'll likely have to get far more specific than it might be worth. With ~40,000 lines to go through I was hoping to limit how manual it might need to be but if not I'll suck it up.

 

[Joe4]

Re: Char(10) Help

Dealing with Names and Addresses can be a real pain, since there are so many different variations.
If it all possible, it is usually to try to deal with them at the source (though I know it is not always possible!).