Flatten Data into Colon Separated Values

Flatten Data into Colon Separated Values

4
NewbieNewbie
4

    Sep 29, 2005#1

    I am trying to load the following information into Excel and would like to flatten the following text into a Colon Separated Table.

    Code: Select all

    1. Centura for Beginners to Programming 
   START1_Questions - Workbook QUESTIONS
   START1_Answers - Workbook ANSWERS
   START1_Orientation - START1 - Orientation
   START1_01 - Lesson 1 - What is Computer Programming?
   START1_02 - Lesson 2 - Computer Programming Workflow
   START1_03 - Lesson 3 - Obtaining the Tools You'll Need
 
  
2. Oracle for Beginners to Programming
   START2_Questions - Workbook QUESTIONS
   START2_Answers - Workbook ANSWERS
   START2_01 - Lesson 1 - What is Computer Programming?
   START2_02 - Lesson 2 - Computer Programming Workflow
   START2_03 - Lesson 3 - Obtaining the Tools You'll Need
   START2_04 - Lesson 4 - Creating a User Interface

    Code: Select all

    "1":"Centura for Beginners to Programming":"START1_Questions":"Workbook QUESTIONS"
"1":"Centura for Beginners to Programming":"START1_Answers":"Workbook ANSWERS"
"1":"Centura for Beginners to Programming":"START1_Orientation":"START1":"Orientation"
"1":"Centura for Beginners to Programming":"START1_01":"Lesson 1":"What is Computer Programming?"
"1":"Centura for Beginners to Programming":"START1_02":"Lesson 2":"Computer Programming Workflow"
"1":"Centura for Beginners to Programming":"START1_03":"Lesson 3":"Obtaining the Tools You'll Need"
"2":"Oracle for Beginners to Programming":"START2_Questions":"Workbook QUESTIONS"
"2":"Oracle for Beginners to Programming":"START2_Answers":"Workbook ANSWERS"
"2":"Oracle for Beginners to Programming":"START2_01":"Lesson 1":"What is Computer Programming?"
"2":"Oracle for Beginners to Programming":"START2_02":"Lesson 2":"Computer Programming Workflow"
"2":"Oracle for Beginners to Programming":"START2_03":"Lesson 3":"Obtaining the Tools You'll Need"
"2":"Oracle for Beginners to Programming":"START2_04":"Lesson 4":"Creating a User Interface"
    Basically I am trying to separate the words between " - ".

    Thanks in advance.
    6,615550
    Grand MasterGrand Master
    6,615550

      Sep 29, 2005#2

      A single regular expression replace can't do that, a macro is needed:

      InsertMode
      ColumnModeOff
      HexOff
      UnixReOff
      InsertMode
      ColumnModeOff
      HexOff
      UnixReOff
      Bottom
      IfColNum 1
      "
      "
      Else
      "

      "
      EndIf
      Top
      TrimTrailingSpaces
      Clipboard 9
      Find RegExp "%^([0-9]+^). ++^(*^)$"
      Replace All ""^1":"^2":""
      Loop
      Find RegExp "%"[0-9]+*$"
      IfNotFound
      ExitLoop
      EndIf
      Cut
      Key DEL
      StartSelect
      Find Select "^p^p"
      Key UP ARROW
      Key END
      Find RegExp "% ++"
      Replace All SelectText "^c"
      Find RegExp "^p"
      Replace All SelectText ""^p"
      Find " - "
      Replace All SelectText "":""
      EndSelect
      EndLoop
      Loop
      Top
      Find "^p^p"
      IfFound
      Top
      Find "^p^p"
      Replace All "^p"
      Else
      ExitLoop
      EndIf
      EndLoop
      ClearClipboard
      Clipboard 0
      UnixReOn

      Remove the last red command, if you use regular expression in UltraEdit style by default instead of Unix style - see Advanced - Configuration - Find - Unix style Regular Expressions. UnixReOn/UnixReOff modifies this setting.

      And don't forget to activate macro property Continue if a Find with Replace not found.
      Best regards from an UC/UE/UES for Windows user from Austria
      4
      NewbieNewbie
      4

        Sep 29, 2005#3

        Worked very well.

        Thanks a lot Mofi.

        -Sanjay