Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    387
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I would like to move everything in a cell from a comma "," to the right of the cell and record it in a cell to the right ( without the comma "," and "see"). I also do not want to have the comma and everything to the right in the original cell.

    This sounds confusing when i look at this so I am including a sample worksheet with two section. One is the "CURRENT" which is how the information currently is and the second is the 'WISH LIST' showing the way I would hope to have a formula show it, in two columns and separated properly. Is this possible?

    Thanks,

    MNN

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is an in-place solution using code:

    Code:
    Sub SplitEm()
      Dim oCell As Range
      Dim arr As Variant
      For Each oCell In Selection
    	arr = Split(oCell, ", see ")
    	If UBound(arr) > 0 Then
    	  oCell = arr(0)
    	  oCell.Offset(0, 1) = arr(1)
    	End If
      Next oCell
    End Sub
    Select the cells with names then run this macro.

    You can't use formulas for an in-place solution because it would cause circular references. You can use formulas to fill the Wish List area: in A14:

    =IF(ISERROR(FIND(", see ",A6)),A6,LEFT(A6,FIND(", see ",A6)-1))

    and in B14:

    =IF(ISERROR(FIND(", see ",A6)),"",MID(A6,FIND(", see ",A6)+6,100))

    Select A14:B14 and fill down to row 17.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    387
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks but I'm not a VBA guy. Could this be a formula driven initiative?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Did you read my entire reply?

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    387
    Thanks
    3
    Thanked 0 Times in 0 Posts
    oops, just noticed the formula, I will try it. Thank you.

Posting Permissions

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