0

How would I write a macro that would search for a specific symbol (in Excel) and replace it with a hard return in the same cell and maintain the cell integrity?

I often convert word documents into excel and prior to converting, I replace all the hard returns in the word document with pound signs (@@) so that the cell integrity is maintained on the paste function in the excel document (if you don't remove the hard returns, a new cell is created at each return).

After the paste into excel, I then need to replace the "@@" signs with hard returns and I cannot find the "hard return" as a choice under "replace". I believe a macro is needed to do this.

Thanks for any help you can give.

0

You can actually do this with the find and replace option.
Change your cell text to: ="this @@ is @@ your @@ cell @@ text".

Then go to find and replace and replace @@ (or whatever you want to replace) with " & char(10) & ". This will change your output from "this @@ is @@ your @@ cell @@ text" to "this is your cell text".

Now you need to format the cells to enable word wrap (right click > format > alignment tab > check word wrap). Your new output should be:
This
is
your
cell
text

0

For a short macro:

Sub DoubleAt()
    Cells.Replace What:="@@", Replacement:=Chr(10)
End Sub
0

This was one of the top hits in google when I searched "hard wrap text excel macro"... so here is my solution for wrapping a cell of text based on a width:

Function hardwrap(useThis As Range, WrapAt As Integer) As String
    Dim i As Integer
    Dim Temp As String
    Dim retVal As String
    i = 0
    retVal = useThis.Value
    With useThis
        If Len(.Value) > WrapAt Then
            Temp = retVal
            Do
                i = i + WrapAt
                Do
                    If Mid(Temp, i, 1) = " " Then
                        Temp = Left(Temp, i - 1) & Chr(10) & Right(Temp, Len(Temp) - i)
                        Exit Do
                    Else
                        i = i - 1
                        If i < 0 Then
                            Exit Do
                        End If
                    End If
                Loop
            Loop While i < Len(Temp) - WrapAt
            retVal = Temp
        End If
    End With
    hardwrap = retVal
End Function

This code is heavily based on this function by Andrew Poulsom found here: http://www.mrexcel.com/forum/excel-questions/424309-macro-wrap-text-hard-returns.html#post2097820

Basically if the string is longer than the wrap at variable, it jumps ahead to the wrap point, and marches backwards looking for spaces. When it finds one, it replaces that space with a newline. Then it repeats for the balance of the string.

My version is a little more generic (IMHO).

Example:

A1:

"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."

A2: =hardwrap(A1,40)

"Lorem ipsum dolor sit amet,
consectetur adipiscing elit, sed do
eiusmod tempor incididunt ut labore et
dolore magna aliqua. Ut enim ad minim
veniam, quis nostrud exercitation
ullamco laboris nisi ut aliquip ex ea
commodo consequat. Duis aute irure
dolor in reprehenderit in voluptate
velit esse cillum dolore eu fugiat
nulla pariatur. Excepteur sint occaecat
cupidatat non proident, sunt in culpa
qui officia deserunt mollit anim id est
laborum."

Hope that helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.