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!

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!

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!

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!
|
|
|

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!
|
|
|