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?
Level 4 (3,240 points)

Will have a look at it - after supper!

• ###### 16. Re: Easy way to strip text from a number?
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?
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.

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.

• ###### 18. Re: Easy way to strip text from a number?
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?
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?
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?
Level 1 (55 points)

Barry, I was being facetious.

• ###### 22. Re: Easy way to strip text from a number?
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