Auto-Insert Line Break (Alt+Enter)?

[Guest]

I have a spreadsheet imported from an outside program. The program is web
based, and has "cases" set up that cust. serv & sales people can "comment" on
back & forth.
When it imports into excel, each comment for each case is an individual row
(so for case 66222, there are 6 rows if there are 6 comments). What I need
to do is combine everything into 1 row.
I'm using the a1&b1&c1 to combine all the comments into one cell, but at the
beginning of each comment, I want to insert a line break. Short of
individually going into each cell and entering one, is there any way to do
it? Maybe an edit/replace type thing? I've set it up so each new comment
begins with "by:". I can add a character to the beginning of that, and
replace it with something else, but I couldn't find anything about how to add
the alt+enter line break.
Any ideas? Thanks in advance!

 

[Jim Rech]

You might try this macro. Select the range of concatenated cells and run
it.

Sub a()
Dim Cell As Range
Dim CellVal As String
For Each Cell In Selection
CellVal = Cell.Value
If CellVal <> "" Then
CellVal = Replace(CellVal, "by: ", Chr(10) & "by: ")
CellVal = Mid(CellVal, 2) ''Don't add CR before first comment
Cell.Value = CellVal
End If
Next
End Sub


--
Jim
|I have a spreadsheet imported from an outside program. The program is web
| based, and has "cases" set up that cust. serv & sales people can "comment"
on
| back & forth.
| When it imports into excel, each comment for each case is an individual
row
| (so for case 66222, there are 6 rows if there are 6 comments). What I
need
| to do is combine everything into 1 row.
| I'm using the a1&b1&c1 to combine all the comments into one cell, but at
the
| beginning of each comment, I want to insert a line break. Short of
| individually going into each cell and entering one, is there any way to do
| it? Maybe an edit/replace type thing? I've set it up so each new comment
| begins with "by:". I can add a character to the beginning of that, and
| replace it with something else, but I couldn't find anything about how to
add
| the alt+enter line break.
| Any ideas? Thanks in advance!

 

[Guest]

Jim,

I have basically the same question except I want to search every cell under
a column and find the end of text in that cell and insert a Alter-Enter. I
already knew how to do that with "Replace - Alt-0010", but what do I search
for to find the end of each line of text in that cell?

This is urgent so any help would be helpful.

 

[Jim Rech]

I don't think you can search for the end of a line of text. I think I'd do
it this way:

Sub a()
Dim Cell As Range
Dim CellVal As String
For Each Cell In Selection
CellVal = Cell.Value
If CellVal <> "" Then
Cell.Value = CellVal & Chr(10)
End If
Next
End Sub


--
Jim
| Jim,
|
| I have basically the same question except I want to search every cell
under
| a column and find the end of text in that cell and insert a Alter-Enter.
I
| already knew how to do that with "Replace - Alt-0010", but what do I
search
| for to find the end of each line of text in that cell?
|
| This is urgent so any help would be helpful.
|
| "Jim Rech" wrote:
|
| > You might try this macro. Select the range of concatenated cells and
run
| > it.
| >
| > Sub a()
| > Dim Cell As Range
| > Dim CellVal As String
| > For Each Cell In Selection
| > CellVal = Cell.Value
| > If CellVal <> "" Then
| > CellVal = Replace(CellVal, "by: ", Chr(10) & "by: ")
| > CellVal = Mid(CellVal, 2) ''Don't add CR before first
comment
| > Cell.Value = CellVal
| > End If
| > Next
| > End Sub
| >
| >
| > --
| > Jim
| > | > |I have a spreadsheet imported from an outside program. The program is
web
| > | based, and has "cases" set up that cust. serv & sales people can
"comment"
| > on
| > | back & forth.
| > | When it imports into excel, each comment for each case is an
individual
| > row
| > | (so for case 66222, there are 6 rows if there are 6 comments). What I
| > need
| > | to do is combine everything into 1 row.
| > | I'm using the a1&b1&c1 to combine all the comments into one cell, but
at
| > the
| > | beginning of each comment, I want to insert a line break. Short of
| > | individually going into each cell and entering one, is there any way
to do
| > | it? Maybe an edit/replace type thing? I've set it up so each new
comment
| > | begins with "by:". I can add a character to the beginning of that, and
| > | replace it with something else, but I couldn't find anything about how
to
| > add
| > | the alt+enter line break.
| > | Any ideas? Thanks in advance!
| >
| >
| >

 

[Guest]

Jim,

Thanks for the help. Just two quick questions. (A) Where do I put in that
script to tell it which column to search? (B) I'm not the greatest at VBA
and played with it a few years ago. Can I just create a blank macro with the
recorder and then just dump that script into it?

Thanks.

Doug