Results 1 to 3 of 3

Thread: excel. put ' ' around every item in a column

  1. #1

    Thread Starter
    Addicted Member DKasler's Avatar
    Join Date
    Jan 2005
    Location
    Brooklyn, NYC
    Posts
    177

    excel. put ' ' around every item in a column

    Hey... Im TOTALLY New to VBA, but I have an xls and i need to put all the items in a column in ' '

    so i need to make coulmn D go from

    a
    b
    c
    d

    TO:

    'a'
    'b'
    'c'
    'd'

    There are over 700 rows in the sheet, otherwise id do it by hand. Any suggestions?
    -----MY SITES-----
    BayRidgeNights.Com - NYC Nightlife Forums

    Fight Communism - Rate Posts!

  2. #2
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: excel. put ' ' around every item in a column

    Hmmm ...

    Here is the Macro, but it shows up a funny behavior ... Excel does not like to display the leading ' character! It shows up in the formula window, but not in the cell. If you want it to show up in the cell, you will have to insert TWO ' characters in the prefix string. Maybe it has something to do with how the cell is formatted.

    Anyway, select the entire range you want to modify and run the macro.
    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 6/1/2005 by guawd1
    '
    Dim acell As Range
    Dim astr As String
    For Each acell In Selection.Cells
        astr = CStr(acell.Value)
        acell.Value = "''" & astr & "'"   'Play around with the number of ' characters
    Next
    '
    End Sub
    Good luck and good learning
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: excel. put ' ' around every item in a column

    if you look in the cell edit window the ' is shown correctly,
    one solution is to put a leading space

    acell.Value = " '" & astr & "'"

    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width