Need to add quotes to cell entries

[Jim]

I have a sheet where I need to add a quote symbol to
the beginning and end of all the entries in the cells in
columns C through BD. Is there an easy to do this?

Ex: 51a00332003 to "51a00332003"

Thanks,

Jim

 

[Random]

If your character set is like mine, this should work:

=CHAR(34)&C1&CHAR(34)

Unless you mean to concatenate all of the columns from C through
BD.... Then you are on your own... :}

Random


ASsuming the data you

 

[Gord Dibben]

Jim

Copy/paste this macro to a general module in your workbook.

Select your range then Tools>Macro>Macros. Select the macro and "Run".

Type " into Input Box and OK.

Sub Add_Text_Left_Right()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo Endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
For Each cell In thisrng
cell.Value = moretext & cell.Value & moretext
Next
Exit Sub
Endit:
MsgBox "only formulas or numbers in range"
End Sub

Gord Dibben Excel MVP XL2002

 

[Jim]

Thanks Gord for the help. It worked great for 90% of
the entries but I have dates, positive and negative numbers
in some cells that didn't get the quotes.

Is there a way around this?

Thanks,

Jim

Jim

Copy/paste this macro to a general module in your workbook.

Select your range then Tools>Macro>Macros. Select the macro and "Run".

Type " into Input Box and OK.

Sub Add_Text_Left_Right()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo Endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
For Each cell In thisrng
cell.Value = moretext & cell.Value & moretext
Next
Exit Sub
Endit:
MsgBox "only formulas or numbers in range"
End Sub

Gord Dibben Excel MVP XL2002

 

[Gord Dibben]

Jim

I incorrectly assumed all the data to be changed would be text and left
numbers and formulas as was. Try the code below with xlTextValues removed.

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo Endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
moretext = InputBox("Enter your Text")
For Each cell In thisrng
cell.Value = moretext & cell.Value & moretext
Next
Exit Sub
Endit:
MsgBox "only formulas in range"
End Sub

Note: if any formulas reference the changed data you will get errors(#VALUE)

If you also want any formula results to also have the quotes change also:

Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)

to: Set thisrng = Range(ActiveCell.Address & "," & Selection.Address)

Gord

Thanks Gord for the help. It worked great for 90% of
the entries but I have dates, positive and negative numbers
in some cells that didn't get the quotes.

Is there a way around this?

Thanks,

Jim