1 2 Previous Next 22 Replies Latest reply: Feb 22, 2013 12:24 PM by Barry Go to original post
  • 15. Re: Easy way to strip text from a number?
    HD Level 4 Level 4 (3,240 points)

    Will have a look at it - after supper!

  • 16. Re: Easy way to strip text from a number?
    Badunit Level 6 Level 6 (10,815 points)

    Here is another solution. It takes each character, one at a time, and tries to subtract  0 from it. If it is a number, the subtraction works and we keep the character. If not a number it generates an error and we replace it with "". I only did 7 characters but all it takes to add another character is copy/paste and change one number.

     

    =IFERROR(MID(A1,1,1)-0,"")&IFERROR(MID(A1,2,1)-0,"")&IFERROR(MID(A1,3,1)-0,"")&I FERROR(MID(A1,4,1)-0,"")&IFERROR(MID(A1,5,1)-0,"")&IFERROR(MID(A1,6,1)-0,"")&IFE RROR(MID(A1,7,1)-0,"")

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

    Phew.

     

    The script now detects the selection on any table of any sheet on the front document (in case the original data isn't in table 1 of sheet 1). It creates a new table on the same sheet as the original data, with the stripped values in a single column. All alpha characters are stripped from the original values, as are the character "+" and anything that comes after it.

     

    Again, copy and paste the script into an AppleScript Editor window.

     

    In Numbers, select your data:

     

    Screen Shot 2013-02-17 at 22.33.15.png

     

    Run the script:

     

    on detect_table()

              tell application "Numbers"

                        tell front document

                                  repeat with x from 1 to count sheets

                                            set this_sheet to sheet x

                                            tell sheet x

                                                      repeat with y from 1 to count tables

                                                                set this_table to table y

                                                                tell table y

                                                                          try

      column of selection range

                                                                                    return {this_table, this_sheet}

                                                                          end try

                                                                end tell

                                                      end repeat

                                            end tell

                                  end repeat

                        end tell

              end tell

    end detect_table

     

    tell application "Numbers"

              try

                        set {this_table, this_sheet} to my detect_table()

              on error number errnum

                        if errnum = -2763 then

                                  display dialog "Select some data and try again"    

                        end if

                        return

              end try

              set the_digits to "1234567890"

     

              set the_cells to cells of selection range of this_table

              set cell_count to count the_cells

              tell this_sheet to set nu_table to (make new table at end with properties {row count: (cell_count + 1), column count:1}) 

              repeat with z from 1 to cell_count

                        set each_cell to item z of 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 target_cell to cell (z + 1) of column 2 of nu_table

                        set value of target_cell to nu_content

              end repeat

    end tell

     

    When I run the script, I get this:

     

    Screen Shot 2013-02-17 at 22.33.28.png

     

    I would strongly suggest that you try it on a copy of your original spreadsheet.

     

    Hope it helps.

     


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

    That looks terrific. Thanks again, HD.

     

    For what this is worth, I have submitted a request to expand on the usefulness of the VALUE() function to include non-currency symbols. I have not heard anything back yet, oddly enough.

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

    Martin,

     

    It's good that you have sent your feedback to Apple. I'm sure that they keep records on such requests and consider the popularity of such suggestions.

     

    The iWork set of productivity apps seem to concentrate on the basics - the features that almost all users are likely to need. I must say that this request of yours hasn't been seen often here, so you may have a bit of a wait before this feature makes it into the app.

     

    Jerry

  • 20. Re: Easy way to strip text from a number?
    Barry Level 7 Level 7 (29,180 points)

    "I have not heard anything back yet, oddly enough."

     

    Nothing odd about that, Martin. Apple is pretty up front about that with this statement on the feedback form: "We read all feedback carefully, but please note that we cannot respond to the comments you submit."

     

    Regards,

    Barry

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

    Barry, I was being facetious.

  • 22. Re: Easy way to strip text from a number?
    Barry Level 7 Level 7 (29,180 points)

    Martin Gallo wrote:

     

    Barry, I was being facetious.

    To quote the late, great Thomas Wright Waller, "One never knows, do one?"

     

    Regards,

    Barry

1 2 Previous Next