Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enter character (Excel 2000)

    I am trying to format data in excel. Right now I am trying to format a column of cells that contain multiple table item numbers. I want the table item numbers to be seperated by a comma like:
    1-2,1-3,2-1
    Some of the cells in the column are set up that way. However, there are cells set up where the table numbers are seperated by enters and not commas. The data in a cell looks like:
    1-2
    1-3
    2-1
    I would like to create a function that runs in an onclick event for a button that changes the enters to commas. Does anyone have an idea how I could write a function that does that or what notation represents the enter key character or even if there is notation like that?

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enter character (Excel 2000)

    The non VBA method would be:
    =SUBSTITUTE(A1,CHAR(10),", ")

    There is probably a more elagent way, but the following should get you started.

    Sub testit()
    Dim ccell As Range

    For Each ccell In Selection
    ccell = Replace(ccell, Chr(10), ", ")
    Next

    End Sub

  3. #3
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enter character (Excel 2000)

    Thanks for the help. It was alot easier to figure out knowing the character is represented by char(10). The line of code I wrote was:
    .Range("E2:E" & (Cell_Count - 1)).Replace What:=Chr(10), Replacement:=",", LookAt:=xlPart

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Enter character (Excel 2000)

    You can also use the symbolic constant vbLf (Lf stands for 'Line feed') instead of Chr(10).

Posting Permissions

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