## Easy way to strip text from a number?

683 Views 22 Replies Latest reply: Feb 22, 2013 12:24 PM by Barry
• Level 4 (3,240 points)
Currently Being Moderated
Feb 17, 2013 10:58 AM (in response to Martin Gallo)

Will have a look at it - after supper!

• Level 6 (10,765 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,"")

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

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.

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:

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

Hope it helps.

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

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

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

Regards,

Barry

• 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?"

Regards,

Barry

Previous Next

#### More Like This

• Retrieving data ...

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