Skip navigation

Easy way to strip text from a number?

680 Views 22 Replies Latest reply: Feb 22, 2013 12:24 PM by Barry RSS
1 2 Previous Next
Martin Gallo Level 1 Level 1 (55 points)
Currently Being Moderated
Feb 17, 2013 7:07 AM

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.

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Feb 17, 2013 7:40 AM (in response to Martin Gallo)

    Martin,

     

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

     

    Jerry

  • Wayne Contello Level 6 Level 6 (12,570 points)
    Currently Being Moderated
    Feb 17, 2013 7:54 AM (in response to Martin Gallo)

    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

  • Wayne Contello Level 6 Level 6 (12,570 points)
    Currently Being Moderated
    Feb 17, 2013 8:08 AM (in response to Martin Gallo)

    This may better exaplain what i did:

     

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

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Feb 17, 2013 8:35 AM (in response to Martin Gallo)

    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

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Feb 17, 2013 8:49 AM (in response to Jerrold Green1)

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

     

    Jerry

  • Wayne Contello Level 6 Level 6 (12,570 points)
    Currently Being Moderated
    Feb 17, 2013 8:51 AM (in response to Jerrold Green1)

    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.

  • HD Level 4 Level 4 (3,240 points)
    Currently Being Moderated
    Feb 17, 2013 9:24 AM (in response to Martin Gallo)

    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

  • Jerrold Green1 Level 7 Level 7 (28,135 points)
    Currently Being Moderated
    Feb 17, 2013 9:50 AM (in response to Martin Gallo)

    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

  • HD Level 4 Level 4 (3,240 points)
    Currently Being Moderated
    Feb 17, 2013 10:03 AM (in response to Jerrold Green1)

    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.

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.