Adding quotation marks to each line of cell contents with carriagereturns
 

Hi All,

I am using Excel as an interface to import some process data into a
modelling tool. One of the limitations of the modelling tool is that
any data in the sheet has to be enclosed in quote or speech marks to
import the data.

I have only just discovered this and have a lot of data that has been
imported into the tool which is now unreadable.

Can anyone provide some VBA that will add speech marks i.e. " sample
text " to each line of text in each cell.

Thanks in advance.

Will

Adding quotation marks to each line of cell contents with carriage returns
 

each line of text in each cell.

If you mean you might have multiple lines in a cell separated with LineFeeds
try the following. Select cells in a column and ensure there are empy cells
to the right, run the macro.

Sub QuoteEm()
Dim i as Long
Dim va
Dim rCell As Range

For Each rCell In Selection
va = Split(rCell, vbLf)
For i = LBound(va) To UBound(va)
va(i) = Chr(34) & va(i) & Chr(34)
Next
rCell.Offset(, 1) = Join(va, vbLf)
Next

End Sub

Regards,
Peter T


"Will" wrote in message
...
Hi All,

I am using Excel as an interface to import some process data into a
modelling tool. One of the limitations of the modelling tool is that
any data in the sheet has to be enclosed in quote or speech marks to
import the data.

I have only just discovered this and have a lot of data that has been
imported into the tool which is now unreadable.

Can anyone provide some VBA that will add speech marks i.e. " sample
text " to each line of text in each cell.

Thanks in advance.

Will

Adding quotation marks to each line of cell contents with carriage returns
 

Sub cellquotes()
Dim cell As Range
For Each cell In Range("A1:A100").Cells
If cell.Value < "" Then
cell.Value = Chr(34) & cell.Value & Chr(34)
End If
Next
End Sub

"Peter T" <peter_t@discussions wrote in message
...
each line of text in each cell.

If you mean you might have multiple lines in a cell separated with
LineFeeds try the following. Select cells in a column and ensure there are
empy cells to the right, run the macro.

Sub QuoteEm()
Dim i as Long
Dim va
Dim rCell As Range

For Each rCell In Selection
va = Split(rCell, vbLf)
For i = LBound(va) To UBound(va)
va(i) = Chr(34) & va(i) & Chr(34)
Next
rCell.Offset(, 1) = Join(va, vbLf)
Next

End Sub

Regards,
Peter T


"Will" wrote in message
...
Hi All,

I am using Excel as an interface to import some process data into a
modelling tool. One of the limitations of the modelling tool is that
any data in the sheet has to be enclosed in quote or speech marks to
import the data.

I have only just discovered this and have a lot of data that has been
imported into the tool which is now unreadable.

Can anyone provide some VBA that will add speech marks i.e. " sample
text " to each line of text in each cell.

Thanks in advance.

Will

Adding quotation marks to each line of cell contents withcarriage returns
 

On 22 July, 11:09, "Patrick Molloy"
wrote:
Sub cellquotes()
Dim cell As Range
For Each cell In Range("A1:A100").Cells
* * If cell.Value < "" Then
* * * * cell.Value = Chr(34) & cell.Value & Chr(34)
* * End If
Next
End Sub

"Peter T" <peter_t@discussions wrote in message

...



each line of text in each cell.

If you mean you might have multiple lines in a cell separated with
LineFeeds try the following. Select cells in a column and ensure there are
empy cells to the right, run the macro.

Sub QuoteEm()
Dim i as Long
Dim va
Dim rCell As Range

* *For Each rCell In Selection
* * * *va = Split(rCell, vbLf)
* * * *For i = LBound(va) To UBound(va)
* * * * * *va(i) = Chr(34) & va(i) & Chr(34)
* * * *Next
* * * *rCell.Offset(, 1) = Join(va, vbLf)
* *Next

End Sub

Regards,
Peter T

"Will" wrote in message
...
Hi All,

I am using Excel as an interface to import some process data into a
modelling tool. *One of the limitations of the modelling tool is that
any data in the sheet has to be enclosed in quote or speech marks to
import the data.

I have only just discovered this and have a lot of data that has been
imported into the tool which is now unreadable.

Can anyone provide some VBA that will add speech marks i.e. " sample
text " to each line of text in each cell.

Thanks in advance.

Will- Hide quoted text -

- Show quoted text -

Great, thanks Peter and Patrick