Skip navigation

Easy way to strip text from a number?

682 Views 22 Replies Latest reply: Feb 22, 2013 12:24 PM by Barry RSS
  • HD Calculating status...
    Currently Being Moderated
    Feb 17, 2013 10:58 AM (in response to Martin Gallo)

    Will have a look at it - after supper!

  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    Feb 17, 2013 1:42 PM (in response to Martin Gallo)

    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,"")

  • HD Level 4 Level 4 (3,240 points)
    Currently Being Moderated
    Feb 17, 2013 2:49 PM (in response to Martin Gallo)



    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


      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"


                        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


              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


                                  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.


  • Jerrold Green1 Level 7 Level 7 (28,160 points)
    Currently Being Moderated
    Feb 18, 2013 4:49 PM (in response to Martin Gallo)



    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.



  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Feb 18, 2013 10:54 PM (in response to Martin Gallo)

    "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."




  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Feb 22, 2013 12:24 PM (in response to Martin Gallo)

    Martin Gallo wrote:


    Barry, I was being facetious.

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




1 2 Previous Next


More Like This

  • Retrieving data ...

Bookmarked By (0)


  • 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.