Auto-Insert Line Break (Alt+Enter)?
 

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!

Auto-Insert Line Break (Alt+Enter)?
 

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
"o1darcie1o" wrote in message
...
|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!

Auto-Insert Line Break (Alt+Enter)?
 

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
"o1darcie1o" wrote in message
...
|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!

Auto-Insert Line Break (Alt+Enter)?
 

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
"dfresh34" wrote in message
...
| 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
| "o1darcie1o" wrote in message
| ...
| |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!
|
|
|

Auto-Insert Line Break (Alt+Enter)?
 

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

"Jim Rech" wrote:

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
"dfresh34" wrote in message
...
| 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
| "o1darcie1o" wrote in message
| ...
| |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!
|
|
|