Need to add quotes to cell entries

  • Thread starter Thread starter Jim
  • Start date Start date
J

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




Gord Dibben said:
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
Click to expand...
 
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
Click to expand...
 
Back
Top