Announcement

Collapse
No announcement yet.

Split Text Across Columns

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Split Text Across Columns

    My problem:
    Need to split the WORD into Col B and put the DEFINITION into Col C.
    Here's an example of what's sitting in A1:
    Title A description of record contents

    I've tried using the text to columns but can't get it to work.
    (since the Words are all different lengths, something gets chopped off)

    What I have today:
    In column A (within a single cell is both the Word & it's Definition).
    I need to extract the word ONLY into a new column (B)
    and extract all the other words into column (C) (without the dash)...

    I've also tried :
    =LEFT(A1, FIND(" ",A1)-1)
    and successfully stripped the Word into column B
    but can't find any functions to extract the rest properly into C

    I JUST noticed, some WORDS are multuple...example:
    Information Protection Level Used to identify information protection values per Pro 2227

    Can you provide a function for doing a 3 word extraction to Col B?
    I guess, what I REALLY need is for it to take "everything up to the dash" and put in column B......then put everything after the dash and put in col C.

  • #2
    Re: Split Words Into Col B, Definition Into Col C

    Hi,

    Try,

    Data > Text to columns.. check Delimited > check Other and type "-" (without quotes) > next > finish.

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Split Words Into Col B, Definition Into Col C

      I already tried exactly that....and it didnt work.
      I went ahead and did it again -- just to confirm.

      I've attached small sample to see for yourself.
      Attached Files

      Comment


      • #4
        Re: Split Words Into Col B, Definition Into Col C

        OK. Instead of typing "-", hit Alt+150

        HTH
        Kris

        ExcelFox

        Comment


        • #5
          Re: Split Words Into Col B, Definition Into Col C

          Or, just copy the - from the formula bar (Ctrl+C) with any cell in Column active. Then paste it in Other with Ctrl+V

          Comment


          • #6
            Re: Split Text Across Columns

            I'm not sure that I'm following you.

            Sounded like you are saying:
            Peform all the original steps and only change one part:
            1)Click on the column header to highlight Col A
            2)Then, Data > Text to columns.. check Delimited >
            3)and when the 2nd screen appears that allows you check boxes such as OTHER (don't check anything) and hit ALT+150 while you're sitting on that screen, then, > click next > finish.

            When I performed the ALT+150 I was on the screen with checkboxes:
            I tried leaving OTHER UN-CHECKED (but cleared the field where the dash went)
            I tried check-marking OTHER and (clearing the dash field)

            I tried simply doing ALT+150 without entering the text to columns tool and still not seeing anything happen.
            8-(

            Something must be missing.....

            Comment


            • #7
              Re: Split Text Across Columns

              OTHER (don't check anything) and hit ALT+150
              check and hit Alt+150

              HTH
              Last edited by Krishnakumar; November 15th, 2006, 16:38.
              Kris

              ExcelFox

              Comment


              • #8
                Re: Split Text Across Columns

                Originally posted by ChrisOK
                When I performed the ALT+150 I was on the screen with checkboxes:
                I tried check-marking OTHER and (clearing the dash field)
                I've already that, buttried it again and again and there's no response after placing a check in the OTHER box, then, while sitting within the empty field to the right of the OTHER box, I hit ALT+150, ....nothing happens so I proceed by hitting Next and Finish.

                Still, nothing happens?!?
                Have you tried this with the attached file to see if it works?

                Comment


                • #9
                  Re: Split Text Across Columns

                  Hi,

                  Try this macro.

                  Sub TestIt()
                  Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                  Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                  :="", FieldInfo:=Array(Array(1, 1), Array(2, 1))
                  End Sub


                  HTH
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Split Text Across Columns

                    Try this way.

                    Comment


                    • #11
                      Re: Split Text Across Columns

                      Thanks Kris, the code worked!!!! It's perfect thank you!

                      Dave, I tried your suggestion and can't seem to get it to work....
                      I'd still like to learn that trick (if you can perhaps provide more detail) on where I might have gone wrong....
                      You said:
                      "Or, just copy the - from the formula bar (Ctrl+C) with any cell in Column active. Then paste it in Other with Ctrl+V"

                      Here's exactly what I tried that did not work:
                      Sounded like you were saying:
                      Peform all the original steps and only change one part:
                      1)Click on one of the cells in Col A
                      2)Go to the formula bar and highlight the dash, copy it and ONLY it..
                      3)Now, click on the column header to highlight Col A (so whatever is about to happen, will happen with all the cells in Col A.
                      2)Then, Data > Text to columns.. check Delimited >
                      3)and when the 2nd screen appears that allows you check boxes such as OTHER (check it) then, paste the dash within the open field
                      4) then, > click next > finish.
                      ???

                      Comment


                      • #12
                        Re: Split Text Across Columns

                        Yep, that is what I did and it worked on your sample.

                        Comment

                        Working...
                        X