Easy way to strip text from a number?

Martin,

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

Jerry

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:

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

This may better exaplain what i did:

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

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

Jerry

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.

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 (countcharacters in the_content)

repeat with x from 1 to char_count

set next_char to characterx 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:

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

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

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 (countcharacters in the_content)

repeat with x from 1 to char_count

set next_char to characterx 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.

Will have a look at it - after supper!