14 Replies Latest reply: Dec 8, 2013 5:05 AM by gestyle
papalapapp Level 1 Level 1 (80 points)

Hi,

 

I'm trying to find out how I can grab a part of a cell content. Example: The cell has the content "1.22.333".

 

Now, I need to just grab the last part of that (and sometimes also the middle part). I tried to use the FIND and RIGHT function but the problem is that they need the amount of digits which can be different (1.2.3 or 1.22.333).

 

How can I grab/calculate just the "1" or "22" or "333" from that?

 

p.


MacBook, OS X Mountain Lion (10.8), 2 GHz Core 2 Duo 1TB + 256 GB SSD
  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    Pap,

     

    You can use the LEN function to determine the length of the original string. It may take more than one use of FIND to extract a substring from the middle.

     

    Jerry

  • Yellowbox Level 5 Level 5 (6,660 points)

    Hi papa,

     

    Here is a workaround, but it involves a word processor such as Pages or Word.

     

    Copy your cells and paste into a blank word processor document.

    In Pages, go to:

     

    Menu > Edit > Find > Find...

    Click on the Advanced button.

     

    Type a full stop ('period' in AmericanSpeak) in the Find box.

    In the Replace box, use the 'Insert' Pop-up menu to insert a Tab.

    Replace All.

     

    Screen Shot 2013-03-13 at 1.25.52 AM.png

    Copy and paste into blank cells in Numbers:

     

    Screen Shot 2013-03-13 at 1.27.09 AM.png

     

    How can I grab/calculate just the "1" or "22" or "333" from that?

     

    Grab what you want!

     

    Regards,

    Ian.

  • papalapapp Level 1 Level 1 (80 points)

    Hi Yellow, thanks for that. I didn't know that I could actually put a tab as a replacement item wich is pretty cool. Unfortunately for my current task I need to stay within Numbers because I work with lists of IDs which I have to process (they are keys for categories).

     

    Jerrold, I have been trying around with LEN but I can't find out how to get the numbers to the right of the first ".".

  • Badunit Level 6 Level 6 (11,400 points)

    There is more than one way to get what you want. Here is one:

     

    Your 111.222.333 numbers are in column A

    You will use four columns (B-E) to separate them.

    B =LEFT(A,FIND(".",A)-1)

    C =RIGHT(A,LEN(A)-FIND(".",A))

    D =LEFT(C,FIND(".",C)-1)

    E =RIGHT(C,LEN(C)-FIND(".",C))

     

    B is the 1's

    D is the 2's

    E is the 3's

    Hide column C

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    papalapapp wrote:

     

    Hi Yellow, thanks for that. I didn't know that I could actually put a tab as a replacement item wich is pretty cool. Unfortunately for my current task I need to stay within Numbers because I work with lists of IDs which I have to process (they are keys for categories).

     

    Jerrold, I have been trying around with LEN but I can't find out how to get the numbers to the right of the first ".".

    It's a bit of a pain. I find that I can get there faster if I use some auxiliary columns so the individual expressions don't get so long. Here's an example:

    Screen Shot 2013-03-12 at 12.26.27 pm.png

    The formulas are as follows:

     

    First Period Position:

     

    =FIND(".", A, 1)

     

    First SubString:

     

    =LEFT(A, FIND(".", A, 1)-1)

     

    Second Period Position:

     

    =FIND(".", A, FIND(".", A, 1)+1)

     

    Middle SubString:

     

    =MID(A, B+1, D-B-1)

     

    Third SubString:

     

    =RIGHT(A, LEN(A)-D)

     

    Regards,

     

    Jerry

  • papalapapp Level 1 Level 1 (80 points)

    Wow, this is so cool. Both of your solutions work perfectly! (Tried to give both of you 10 points, hope it's ok...)

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    You did give us both a 10. What a gentleman!

     

    Regards,

     

    Jerry

  • Barry Level 7 Level 7 (29,210 points)

    As Badunit says (and Ian and Jerry demonstrate), there's often more than one way to get what you want.

     

    Here's one more, using no auxiliary columns:

    Picture 3.png

    B2: =LEFT(A,FIND(".",A)-1)

    C2: =MID(A,FIND(".",A)+1,FIND(".",A,(FIND(".",A)+1))-FIND(".",A)-1)

    D2: =RIGHT(A,LEN(A)-FIND(".",A,(FIND(".",A)+1)))

     

    Interesting exercise. as can be seen in the last row, this solution also works with words (as do the others—they all treat what's in the source column as a text string; the actual contents of that string, except for the two periods, doesn't matter.

     

    Regards,

    Barry

  • papalapapp Level 1 Level 1 (80 points)

    Yes indeed this is neat. I also made some "daisy-chaining" and replaced the column references to pack it all in one formula. Now the table can be sorted and grouped according to the categories. With LOOKUP (SVERWEIS) I placed the name of the category next to it so the user can quickly see what it is.

     

    Cagegories.png

  • gestyle Level 1 Level 1 (0 points)

    Hello!

     

    I’m trying to also extract something in a similar way. Out of this string: “2013-12-08” I’d like to return the “12“ (for December).

     

    What would be the formula therefor, please, assuming the string is located in a cell called “B3”?

     

     

  • Badunit Level 6 Level 6 (11,400 points)

    Using Barry's formula with some slight editing:

     

    =MID(B3,FIND("-",B3)+1,FIND("-",B3,(FIND("-",B3)+1))−FIND("-",B3)−1)

  • SGIII Level 5 Level 5 (5,590 points)

    And of course life is simpler if your data happens to be uniform length and format. In that happy case, you can just do:

     

      =MID(B3,6,2), displaying Text, with the leading zero.

     

      =MONTH(B3) can work too, displaying Number without the leading zero (unless you format it as Numeral System Base 10 Places 2, a trick learned from Jerry)

     

    SG

  • gestyle Level 1 Level 1 (0 points)

    Hey Badunit,

     

    Thank you for your answer! Now it works!

     

    I’d just like to mention one thing I encountered inbetween. I think it’s due to the fact I use OSX in German and started NUMBERS in English via an app called Language Switcher.

     

    First, my formula looked like that:2013-12-08_NUMBERS_DATE-PATTERN.33.5.png

    I tried to compare my line (the upper one) with yours (the one beneath), but both of them brought the same  syntax error message:
    2013-12-08_NUMBERS_DATE-PATTERN.33.6.png
    I thought the error would come from the hyphens (minus dashes) but at the end, it was due to the comma. Replacing the commas by semicolons fixes the issue and now returns the correct value:2013-12-08_NUMBERS_DATE-PATTERN.33.7.png

     

    Cool

  • gestyle Level 1 Level 1 (0 points)

    Hey SGIII,

     

    thank you for your message!

     

    Both of your solutions work!

     

    Also here I faced the same syntax issue as described in my previous answer to Badunit. I think it’s worth to show what happens, in case someone faces the same problem (NUMBERS says an argument is missing but in fact, it simply did not recognized “B3” because of the comma-thing) :
    2013-12-08_NUMBERS_DATE-PATTERN.33.8.png

     

    Next step: replacing comma by semicolon in both espressions solved the problem:
    2013-12-08_NUMBERS_DATE-PATTERN.33.10.png2013-12-08_NUMBERS_DATE-PATTERN.33.9.png

    Perfect!