1 2 Previous Next 22 Replies Latest reply: Feb 22, 2013 12:24 PM by Barry
Martin Gallo Level 1 Level 1 (55 points)

I know that I can use: =RIGHT(G1,LEN(G1)-1) to strip the "j" when cell G1 contains "j450" and return a numerical value of 450.

 

I am just wondering if there is a single Formula to ignore the text part of a cell entry for those inconvenient occasions when cell G1 contains "rj450". In other spreadsheets I have used Formulas such as "texttonum" but cannot seem to locate the equivalent in Numbers. I would have thought that Value(G1) would have worked, but it cannot seem to handle string characters other than currency symbols.

 

I assume the better method will involve the Value() function, but I am still not sure how to strip out the non-numeric characters. I do know that the alpha characters will all be leading, so LEN can still be part of the formula and I just need to know how to count out the leading alpha characters.

  • 1. Re: Easy way to strip text from a number?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Martin,

     

    Do you know what the maximum number of leading alpha characters could be?

     

    Jerry

  • 2. Re: Easy way to strip text from a number?
    Wayne Contello Level 6 Level 6 (13,615 points)

    Martin,

     

    The most general form of the solution is to releatedly pass the cell G1 to a substitute function where you replace each numeric charater with nothing and pass that through one more substitute function where the resulting string is replaced with nothing (to remove the string and leave the numeric characters)then finally  pass through the function value() to get a numeric value.  This means upto 11 nested substitute functions.

     

    Something like:

    Screen Shot 2013-02-17 at 9.53.38 AM.png

    B1=VALUE(SUBSTITUTE(A1, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SU BSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "9", ""), "8", ""), "7", ""), "6", ""), "5", ""), "4", ""), "3", ""), "2", ""), "1", ""), "0", ""), ""))

     

    If you could show a representative example there may be some slicker solutions but, best I can tell, there is no way to directly strip the alpha characters.

     

    Maybe someone else will have a single function solution.  I will also think about this a little more.

     

    Regards,

    Wayne

  • 3. Re: Easy way to strip text from a number?
    Martin Gallo Level 1 Level 1 (55 points)

    Unfortunately, no. My sample data has 1, but more are possible.

  • 4. Re: Easy way to strip text from a number?
    Martin Gallo Level 1 Level 1 (55 points)

    Well, YIKES!!!

     

    Perhaps it is timeto suggest something like this to Apple? Value seems like a likely candidate for modification, possibly adding optional exclusions for decimals and comas?

  • 5. Re: Easy way to strip text from a number?
    Wayne Contello Level 6 Level 6 (13,615 points)

    This may better exaplain what i did:

     

    Screen Shot 2013-02-17 at 10.07.58 AM.png

  • 6. Re: Easy way to strip text from a number?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Martin Gallo wrote:

     

    Well, YIKES!!!

     

    Perhaps it is timeto suggest something like this to Apple? Value seems like a likely candidate for modification, possibly adding optional exclusions for decimals and comas?

    Hang on, I think we may be able to cut that down a bit. It sure would help if you knew more about the limits of the alpha part.

     

    Jerry

  • 7. Re: Easy way to strip text from a number?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Your string could also be parsed more efficiently if you know more about the numeric part.

     

    Jerry

  • 8. Re: Easy way to strip text from a number?
    Wayne Contello Level 6 Level 6 (13,615 points)

    If you could show a representative example there may be some slicker solutions but, best I can tell, there is no way to directly strip the alpha characters.

     

     

    I agree.

  • 9. Re: Easy way to strip text from a number?
    Martin Gallo Level 1 Level 1 (55 points)

    To the best of my knowledge it will be 1 alpha, then from1 to 3 numeric and I have discovered that there may be a '+' or '-' followed by one more numeric tacked on. (I am happy to ignore the ±N part). The two leading alphas were bad data.

     

    So at a minimum I am likely to see data from: 'j1' out to 'j300+5' (and yes the 'j' changes).

  • 10. Re: Easy way to strip text from a number?
    HD Level 4 Level 4 (3,240 points)

    Hi,

     

    This can be AppleScripted. Launch AppleScript Editor and copy and paste the following script:

     

    tell application "Numbers"

    set the_cells to cells of selection range of table 1 of sheet 1 of document 1

    set the_digits to "1234567890"

    repeat with each_cell in the_cells

              set the_content to value of each_cell

              set nu_content to ""

              set char_count to (count characters in the_content)

              repeat with x from 1 to char_count

                        set next_char to character x of the_content

                        if next_char is in the_digits then set nu_content to nu_content & next_char

              end repeat

              set value of each_cell to nu_content

    end repeat

    end tell

     

    In Numbers, select the cells you want to change:

     

    Screen Shot 2013-02-17 at 17.13.44.png

     

    This is the column B in the screen shot.

     

    The script will replace the cells in column B with the numeric content only. (Shown in column D.)

     

    BEWARE: this will remove the original data from column B. If you need to keep it for any reason, then work on a copy. Alternatively, the script could be improved to place the results in a new column.

     

    (You can repeatedly Cmd-Z to undo each replacement, but I'm not sure what would happen on a very long selection.)

     

    Hope this helps.

     

     

    EDIT: if you want to include the "+5" element, then add the character + to the quoted string in the third line of the script.

    EDIT: Updated script to include application "Tell" block

     

    Message was edited by: HD

  • 11. Re: Easy way to strip text from a number?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Martin Gallo wrote:

     

    To the best of my knowledge it will be 1 alpha, then from1 to 3 numeric and I have discovered that there may be a '+' or '-' followed by one more numeric tacked on. (I am happy to ignore the ±N part). The two leading alphas were bad data.

     

    So at a minimum I am likely to see data from: 'j1' out to 'j300+5' (and yes the 'j' changes).

    I was going to concede that Wayne's solution is the best I could imagine, but this complexity is even out of the range of his expression, and would complicate the script solution.

     

    Jerry

  • 12. Re: Easy way to strip text from a number?
    HD Level 4 Level 4 (3,240 points)

    This will ignore the "+" character and everything after it.

     

    tell application "Numbers"

              set the_cells to cells of selection range of table 1 of sheet 1 of document 1

              set the_digits to "1234567890"

              repeat with each_cell in the_cells

                        try

                                  set the_content to ((value of each_cell) as integer) as text

                        on error

                                  set the_content to (value of each_cell) as text

                        end try

                        set nu_content to ""

                        set char_count to (count characters in the_content)

                        repeat with x from 1 to char_count

                                  set next_char to character x of the_content

                                  if next_char is "+" then exit repeat

                                  if next_char is in the_digits then set nu_content to nu_content & next_char

                        end repeat

                        set value of each_cell to nu_content

              end repeat

    end tell

     

    Message was edited by: HD - to catch the case when a cell's original content is an integer.

  • 13. Re: Easy way to strip text from a number?
    Martin Gallo Level 1 Level 1 (55 points)

    Now that is getting fancy. I would like to keep the original data so dropping the new text into a new table is the way to go. Thanks very much!

     

    I will provide feedback to Apple that this seemingly very simple function should probably be available in Numbers as a simple function.

  • 14. Re: Easy way to strip text from a number?
    Martin Gallo Level 1 Level 1 (55 points)

    By the way, Wayne, I understood your suggested solution, it just seemed to be more typing than I thought should be required (which is NOT your fault). Your solution is clever and I wish I had thought of it myself!

1 2 Previous Next