Replace Text String With New Line. Line Break.

Hi All,

I couldn't find another question like this in the forums, but hopefully this is fast and easy to do.

Would anyone know:
How to write a macro that will auto-replace the text string: %newline% with a new line within a cell (preferably on-the-fly) (similar to hitting ALT-Enter within a cell).

The %newline% text string can appear in any cell in the workbook, and can also happen more than once in a cell.

Thanks very much for your help!
CG

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Aaron Blood

Do you want to automatically scan an entire sheet or work within a specified range?

Do the entries look like this:
"Hello%newline%There"

...or this:
"Hello %newline% There"

...and in the second case, do you wish to preserve the leading and trailing blank spaces?

Display More

I'd like to scan the entire worksheet, if possible (no specific ranges). The original can look like either case above (with or without leading and trailing spaces) and can appear multiple times in each column -- It would be nice to remove the leading/trailing spaces, if they exist... but it's not totally needed if it's too hard to figure out how to do. -- Thanks so much in advance!

CG

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Aaron Blood

I like Turtle's starting point.

A few extra lines should take care of the blank spaces... Logically, you just always look for the biggest strings first.

Code

Sub Test()
    Cells.Replace " %newline% ", vbLf
    Cells.Replace "%newline% ", vbLf
    Cells.Replace " %newline%", vbLf
    Cells.Replace "%newline%", vbLf
End Sub

Thanks Aaron & Turtle! This works pretty nice! I had a feeling it was going to be relatively short code, but didn't know it done in just 4 lines! Pretty nice!

I just have 1 more question to ask though, if OK:
May I ask how to make the conversion happen on-the-fly? (meaning, can it convert as soon as I hit enter after typing %newline% into the cell?)

Thanks again for your help!
CG

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Aaron Blood

Do you want it to happen on a specific sheet or for any worksheet in a workbook?

If it were me doing the typing, I'd shorten the string to: %LF

...or are you copy/pasting it in there?

It would be nicer if it worked on any sheet in the workbook, but if it's easier to do on a specific sheet only, that's OK too.

The data already exists in a .CSV file (and already has the %newline% in the cells). Because the data isn't always in the same cells, it's a lot easier to do a quick copy/paste from some cells in the .CSV file into this workbook with the macro.

Having the new lines convert automatically without manually running the macro each time makes things a lot easier since I copy/paste different cells from that file often.

Thanks in advance again!
CG[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I think I've got it. I've attached the code below for anyone that might stumble into this thread in the future. Also thanks again to Aaron and Turtle who I could have not done this without!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Cells.Replace " %newline% ", vbLf
Cells.Replace "%newline% ", vbLf
Cells.Replace " %newline%", vbLf
Cells.Replace "%newline%", vbLf
Application.EnableEvents = True
End Sub

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Dave Hawley

chatguy, please use code tags as YOU agreed.

Done. Apologies. I simply forgot to do that. That's all...I did not mean to violate any agreement by forgetting once.