HenriRaSu

Q: Split text in numbers

Hello, how can i split text in different cells?

 

PS: text to columns workflow not working...

Posted on Feb 19, 2016 12:15 PM

Close

Q: Split text in numbers

  • All replies
  • Helpful answers

  • by SGIII,

    SGIII SGIII Feb 19, 2016 12:30 PM in response to HenriRaSu
    Level 6 (10,681 points)
    Mac OS X
    Feb 19, 2016 12:30 PM in response to HenriRaSu

    What kind of text?  What workflow?

     

    SG

  • by HenriRaSu,

    HenriRaSu HenriRaSu Feb 19, 2016 12:36 PM in response to HenriRaSu
    Level 1 (0 points)
    Feb 19, 2016 12:36 PM in response to HenriRaSu

    The text to column automator says error cuse the variable csvString is not defined

  • by HenriRaSu,

    HenriRaSu HenriRaSu Feb 19, 2016 12:45 PM in response to SGIII
    Level 1 (0 points)
    Feb 19, 2016 12:45 PM in response to SGIII

    Ok, thanks for helping

     

    I'm working in numbers and i have to separate my data like this:


    My data, that i copy and paste from pages - first name and last name(in the same cell) | email - 2 cells

    I want - email | First name | Last name - 3 cells

    -------

    I don't know what i'm doing wrong, becuse i tried the formula that i found in the internet, and got nothing. Same with your tips about the ' text to columns automator workflow '. Says that variable csvString is not defined.

    PS : Sorry for the bad english, i'm brazilian.

    Thanks,

  • by SGIII,Solvedanswer

    SGIII SGIII Feb 19, 2016 1:19 PM in response to HenriRaSu
    Level 6 (10,681 points)
    Mac OS X
    Feb 19, 2016 1:19 PM in response to HenriRaSu

    So if I understand you correctly, after pasting in from Pages you have something like this?

     

    Screen Shot 2016-02-19 at 4.01.34 PM.png

     

    And you want to split the name into First and Last name, with First in one column and Last in another?

     

    If so, you can do this:

     

    Select the cells with the data you want to split, here A2:A3:

     

    Screen Shot 2016-02-19 at 4.02.44 PM.png

     

     

    With the cells still selected, choose 'Text to Columns' from the Numbers > Services menu and choose Space as your delimiter (I'm assuming there's a space between the first and last name).

     

    Screen Shot 2016-02-19 at 4.05.08 PM.png

     

    Click once in cell C2 and command-v or Edit > Paste and Match Style, giving you this:

     

    Screen Shot 2016-02-19 at 4.07.41 PM.png

     

    Add header labels for columns C and D and delete column A, resulting in:

     

    Screen Shot 2016-02-19 at 4.09.16 PM.png

     

    SG

     

     

    P.S. This assumes you are using this Automator Service (Dropbox download).

  • by Wayne Contello,

    Wayne Contello Wayne Contello Feb 19, 2016 1:27 PM in response to HenriRaSu
    Level 6 (19,126 points)
    iWork
    Feb 19, 2016 1:27 PM in response to HenriRaSu

    you can make sure the text fields are separated by tabs and Numbers will place each field in the next cell of a row

     

    if you want to split based on some other criteria, you can perform a search and replace so that each delimiter is replaced by a tab

     

    More details of your specific situation is better

     

    replace the "|" with a tab, then paste into a table

  • by HenriRaSu,

    HenriRaSu HenriRaSu Feb 19, 2016 2:08 PM in response to SGIII
    Level 1 (0 points)
    Feb 19, 2016 2:08 PM in response to SGIII

    First, i did everything that you said. Pass by Pass.

    Captura de Tela .png

    Then:

    Captura de Tela  2.png


    But, before i can choose the ' csv to tabs on Clipboard ', this message comes:

    Captura de Tela 3.png

     

    Then:

     

    Captura de Tela 5.png

     

    After this nothing happens, and comes this message:

     

    Captura de Tela 4.png

     

    Thanks,

  • by SGIII,

    SGIII SGIII Feb 19, 2016 3:09 PM in response to HenriRaSu
    Level 6 (10,681 points)
    Mac OS X
    Feb 19, 2016 3:09 PM in response to HenriRaSu

    Hi, I've retested the Service here and don't get an error... UNLESS I have Numbers 2 (part of iWork '09) open at the same time.  This Service only works with Numbers 3 and will get confused if Numbers 2 is also open, even if you have a different document open in Numbers 2.   Could that be the problem on your machine?

     

    SG

  • by HenriRaSu,

    HenriRaSu HenriRaSu Feb 19, 2016 3:14 PM in response to SGIII
    Level 1 (0 points)
    Feb 19, 2016 3:14 PM in response to SGIII

    Actually, haha i don't know. I have a macbook air 2015 OX el capitan, and the version of numbers is 3.6.1(2566).
    If there's a problem on my machine, i can't tell...

  • by SGIII,

    SGIII SGIII Feb 19, 2016 4:48 PM in response to HenriRaSu
    Level 6 (10,681 points)
    Mac OS X
    Feb 19, 2016 4:48 PM in response to HenriRaSu

    HenriRaSu wrote:

     

    Actually, haha i don't know. I have a macbook air 2015 OX el capitan, and the version of numbers is 3.6.1(2566).
    If there's a problem on my machine, i can't tell...

     

    I didn't mean to suggest there is a problem on your machine!  I meant perhaps the reason the Service was having problems was because you had Numbers 2 open along with Numbers 3.  Many people run both versions.

     

    There may be something with Brazilian localization the Service does not like.  It's hard to debug from here.  If you only need to split first name and last name then you can use a much simpler script (there is also a way to do this with formulas that is not hard).

     

    The simpler script is below.  Just copy-paste it into Script Editor (in your Applications > Utilities folder).

     

    Then select the cells that contain the first and last names together (A2:A3 in my example, C2 in your example) and click the triangle 'run' button in Script Editor.  Then click once in a destination cell (in my example C2) and type command-v or choose Edit > Paste and Match Style to paste the split text into separate columns.

     

    SG

     

    tell application "Numbers"

      tell front document to tell active sheet

      tell (first table whose selection range's class is range)

      set selRange to selection range

      set pasteStr to ""

      tell selRange to repeat with c in cells

      set v to c's value as text

      set f to v's first word

      set l to v's last word

      set pasteStr to pasteStr & f & tab & l & return

      end repeat

      end tell

      end tell

    end tell

     

    set the clipboard to pasteStr

    return pasteStr

  • by HenriRaSu,

    HenriRaSu HenriRaSu Feb 21, 2016 9:12 AM in response to SGIII
    Level 1 (0 points)
    Feb 21, 2016 9:12 AM in response to SGIII

    SG, sorry for the trouble that i'm giving, but this is what happens when i pasted in the script editor.

     

     

     

     

     

    Captura de tela 6.png

     

     

     

    Maybe we can try a formula? I've tried already, but numbers always response with error of sintaxe. You know the right formula that we can use?

     

    Thanks,

     

    HRS1

  • by SGIII,

    SGIII SGIII Feb 21, 2016 9:30 AM in response to HenriRaSu
    Level 6 (10,681 points)
    Mac OS X
    Feb 21, 2016 9:30 AM in response to HenriRaSu

    Hi HRS1,

     

    That's a message you get when Numbers can't find an open document. 

     

    For this script to work you must first:

     

    1. Already have Numbers 3 open (and Numbers 2 must not be open).
    2. Already have a document open in Numbers 3.
    3. Already have selected cells in that document.

     

     

    Also, make sure Script Editor is checked at System Preferences > Security & Privacy:

     

    Screen Shot 2016-02-21 at 12.28.47 PM.png

     

     

    SG

  • by SGIII,

    SGIII SGIII Feb 21, 2016 9:40 AM in response to HenriRaSu
    Level 6 (10,681 points)
    Mac OS X
    Feb 21, 2016 9:40 AM in response to HenriRaSu

    The script should be easier to use, but if you always have a first name followed by a blank followed by a last name, then you can do something like this:

     

    Screen Shot 2016-02-21 at 12.38.02 PM.png

     

    In cell B2, copied down the column:  =LEFT(A2,FIND(" ",A2,1)−1)

    In cell C2, copied down the column:  =RIGHT(A2,LEN(A2)−FIND(" ",A2,1))

     

    SG

  • by HenriRaSu,

    HenriRaSu HenriRaSu Feb 22, 2016 4:25 AM in response to SGIII
    Level 1 (0 points)
    Feb 22, 2016 4:25 AM in response to SGIII

    Hey,

     

    SG, i was trying everything that you said and didin't work. haha

     

    And finally i got the click, i changed the language in system preferences for the english U.S and like magic everything worked, automator, the script editor.. all that.

     

    Thank you for helping me!

     

    HRS

  • by SGIII,

    SGIII SGIII Feb 22, 2016 7:50 AM in response to HenriRaSu
    Level 6 (10,681 points)
    Mac OS X
    Feb 22, 2016 7:50 AM in response to HenriRaSu

    HenriRaSu wrote:

     

    i changed the language in system preferences for the english U.S and like magic everything worked, automator, the script editor.. all that.

     

     

    Hi HRS,

     

    That's interesting that language settings affect the script.  I've looked at the code again but don't know how to tweak it so that at it can run with the region set to Brazil.  Anyway, glad you were finally able to get it to work.  Thanks for the green tick!

     

    SG