Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Ontario, Canada
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text/Cell Manupilation (2003)

    I have raw data separated by comma in a single cell that needs to be extracted and listed either vertically or horizontally.

    Eg. in cell A1 I have: M24KU1, M24KU2, M24KU3, MKU1, MKU2, etc..

    How do I separate this? I don't wan't to use text import wizard, I'd rather use a function if possible.

    Thanks.

    Jay.

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

    Re: Text/Cell Manupilation (2003)

    You could create a custom function in a module in the Visual Basic Editor:

    Function SplitVal(strVal As String)
    SplitVal = Split(strVal, ", ")
    End Function

    Select one or more cells to the right of cell A.
    Enter the formula =SplitVal(A1)
    Press Ctrl+Shift+Enter to confirm the formula as an array formula.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text/Cell Manupilation (2003)

    If you want the split values in the cells directly to the right of the cell containing the string (in your example cells B1:G1) then you could put the following code into a module in the workbook, select all of the cells containing the strings, and then run the macro:

    <code>
    Public Sub SplitString()
    Dim oCell As Range
    Dim I As Long
    Dim strWk As String
    For Each oCell In Selection
    If TypeName(oCell.Value) = "String" Then
    I = 1
    strWk = Trim(oCell.Value)
    If Right(strWk, 1) <> "," Then strWk = strWk & ","
    Do While Len(strWk) > 0
    oCell.Offset(0, I) = Trim(Left(strWk, InStr(strWk, ",") - 1))
    I = I + 1
    strWk = Trim(Right(strWk, Len(strWk) - InStr(strWk, ",")))
    Loop
    End If
    Next oCell

    End Sub
    </code>
    Legare Coleman

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

    Re: Text/Cell Manupilation (2003)

    But isn't that more or less the equivalent of the Data | Text to Columns menu option?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text/Cell Manupilation (2003)

    Yup, but you don't have the fun of writing code. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> With one other difference, Data/Text to columns eliminates the original string which is probably what is wanted anyway.
    Legare Coleman

Posting Permissions

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