Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Separating data in a cell into component parts (XP & 2003)

    I want to know if data in a cell can be separated into various cells. I am including a worksheet to show what I mean. Column A is the current state of the data, labeled "INPUT" and columns B thru H indicate how the result of the separation into many cells in that row.

    Thanks

    MNN

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

    Re: Separating data in a cell into component parts (XP & 2003)

    I cannot determine a consistent "rule" for splitting the data. For example, why should "M DRGDET" from A4 stay together, but "LOCUM TEN" from A11 be split? And why should the next to last part from A2:A9 be split, but from A10:A18 be kept together?
    Unless you can provide a comprehensive and clear set of "rules", I don't think I can do anything.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating data in a cell into component parts (XP & 2003)

    Hans,

    I separated the example into 2 divisions. "A2 - A9" is one set. and "A14 - A22" is the second set. The divisions are usually "/" or "-". I rearranged the separation output to rflect this. The only other issue is to discard the characters "space, paren, numbers, close paren. These items are not needed.

    I am attaching the reconfigured worksheet.

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

    Re: Separating data in a cell into component parts (XP & 2003)

    But why should WL1032WM become WL1032 and WM? There is no separator character.

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

    Re: Separating data in a cell into component parts (XP & 2003)

    And in " DUESSUBS/O-ANE-30808804 (190)", why should SUBS be omitted, ANE be converted to ER and 30808804 be converted to 20108804? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Separating data in a cell into component parts (XP & 2003)

    Since there is no logic to the way you want to split the values, this macro is the best I can do:

    Sub SplitEm()
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim strVal As String
    Dim lngCol As Long
    Dim intPos As Integer
    Dim intPrev As Integer
    Dim c As String

    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = 2 To lngMaxRow
    strVal = Trim(Range("A" & lngRow))
    If Not strVal = "" Then
    lngCol = 2
    intPos = InStr(strVal, " (")
    strVal = Left(strVal, intPos - 1)
    intPrev = 1
    For intPos = 1 To Len(strVal)
    c = Mid(strVal, intPos, 1)
    Select Case c
    Case "", "/", "-"
    Cells(lngRow, lngCol) = Mid(strVal, intPrev, intPos - intPrev)
    lngCol = lngCol + 1
    intPrev = intPos + 1
    End Select
    Next intPos
    Cells(lngRow, lngCol) = Mid(strVal, intPrev)
    End If
    Next lngRow
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating data in a cell into component parts (XP & 2003)

    You are absolutely correct. There is very little logic the way these items were set up. That is why I am trying to untangle this. The last 2 characters is part of the overall scheme only and although there is no separators, they are important to the meaning.
    we need the separation to reformat in a consistent, logical way.

    Martin

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating data in a cell into component parts (XP & 2003)

    Sorry, this is my error. The separation should be ANE not ER.

    Martin.

  9. #9
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating data in a cell into component parts (XP & 2003)

    Is it possible to write a formula for each of the out-put cells to record only those items listed in the sample? I am not at all terrific with macros.

    Thank you

    Martin

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

    Re: Separating data in a cell into component parts (XP & 2003)

    Sorry, I don't understand

  11. #11
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating data in a cell into component parts (XP & 2003)

    For instance, in the "INPUT" cell A2 could formulas be written in cells B2, C2, D2, E2, F2 to derive the output as shown. The same for the input A14 and output cells B14, C14, D14, E14.

    Hopefull, this is a little clearer.

    Thanks,

    Martin

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

    Re: Separating data in a cell into component parts (XP & 2003)

    I still fail to understand the logic behind the way the values are split up.
    For example, why does MED-ATTPD become MED and ATTPD, but WL-1011WB becomes WL1011 and WB?
    And why does PD-ADM stay together, while PD-RAD becomes PD and RAD?
    Whether you do it in code or using formulas, there must be some kind of consistency in the way the values are split. If there isn't, you must do it all manually.

  13. #13
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating data in a cell into component parts (XP & 2003)

    From a consistency view point I certainly understand what you are saying. It took a while for me to ignore/ understand the inconsistencies and be able to read this mumbo-jumbo. That is exactly why I am trying redo these serial identifiers.

    Could we write the formulas stating that all "-", "/", " (" are separators. At least this will give somewhat of a start.

    Thanks Hans.

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

    Re: Separating data in a cell into component parts (XP & 2003)

    As you wish.

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating data in a cell into component parts

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>Coming (more than) a bit late in the day to this exercise, it looks as we have a number of separate "departments" each going their own sweet way in coming up with a labeling system. Would there be any sense in using Select Case - on the (perhaps erroneous) basis that each "department" applies its own (sub-)system consistently? HTH
    Gre

Page 1 of 2 12 LastLast

Posting Permissions

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