Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear All,

    We are currently using M/S excel 2007. One of my coworkers has a worksheet that has several order numbers in one cell. She needs each order number place in a separate cell without the commas.
    I'm attaching a sample of the spreadsheet since I'm not very good at explaining this. The yellow highlighted part is what she wants the finished product to look like (in a separate cell on the worksheet).
    Can someone help me with this please?
    Best regards,
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    You could use the following macro:

    Code:
    Sub ConvertOrders()
      Dim r As Long
      Dim m As Long
      Dim c As Long
      Dim strVal As String
      Dim arrVals
      m = Cells(2, 1).End(xlDown).Row
      For r = 3 To m
    	' Get cell value without the word Order
    	strVal = Mid(Cells(r, 1), 7)
    	' Split value at commas
    	arrVals = Split(strVal, ",")
    	' Loop through the parts
    	For c = 0 To UBound(arrVals)
    	  ' Fill cell with trimmed part
    	  Cells(r, c + 2) = Trim(arrVals(c))
    	Next c
      Next r
    End Sub
    Don't forget to save the workbook as a macro-enabled Excel 2007 workbook (extension .xlsm)

  3. #3
    New Lounger
    Join Date
    Apr 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks I'll give it a shot.

  4. #4
    New Lounger
    Join Date
    Apr 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i don't have that version of excel i have 2003.

    Can you save this in the personal .xlx format.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In Excel 97-2003 the extension for a workbook is .xls whether it contains macros or not. In Excel 2007, there is a difference: .xlsx files cannot contain macros, .xlsm files can.

  6. #6
    New Lounger
    Join Date
    Apr 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there another way I can run this micro in the current format we keep getting an error.

    Help please

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please provide sufficient information.
    What is the format you're currently using?
    What does the error message say?

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='brassman' post='773151' date='30-Apr-2009 15:47']Is there another way I can run this micro in the current format we keep getting an error.

    Help please[/quote]

    Further to Hans' request, please provide a spreadsheet with the last two successfully processed source cells and the processed results; additionally the following two unprocessed or partially processed source cells along with any results.
    Regards
    Don

Posting Permissions

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