## Easy way to strip text from a number?

650 Views 22 Replies Latest reply: Feb 22, 2013 12:24 PM by Barry
Previous Next
Level 1 (55 points)
Currently Being Moderated
Feb 17, 2013 7:07 AM

I know that I can use: =RIGHT(G1,LEN(G1)-1) to strip the "j" when cell G1 contains "j450" and return a numerical value of 450.

I am just wondering if there is a single Formula to ignore the text part of a cell entry for those inconvenient occasions when cell G1 contains "rj450". In other spreadsheets I have used Formulas such as "texttonum" but cannot seem to locate the equivalent in Numbers. I would have thought that Value(G1) would have worked, but it cannot seem to handle string characters other than currency symbols.

I assume the better method will involve the Value() function, but I am still not sure how to strip out the non-numeric characters. I do know that the alpha characters will all be leading, so LEN can still be part of the formula and I just need to know how to count out the leading alpha characters.

• Level 7 (27,380 points)
Currently Being Moderated
Feb 17, 2013 7:40 AM (in response to Martin Gallo)

Martin,

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

Jerry

• Level 6 (11,905 points)
Currently Being Moderated
Feb 17, 2013 7:54 AM (in response to Martin Gallo)

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

• Level 6 (11,905 points)
Currently Being Moderated
Feb 17, 2013 8:08 AM (in response to Martin Gallo)

This may better exaplain what i did:

• Level 7 (27,380 points)
Currently Being Moderated
Feb 17, 2013 8:35 AM (in response to Martin Gallo)

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

• Level 7 (27,380 points)
Currently Being Moderated
Feb 17, 2013 8:49 AM (in response to Jerrold Green1)

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

Jerry

• Level 6 (11,905 points)
Currently Being Moderated
Feb 17, 2013 8:51 AM (in response to Jerrold Green1)

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.

• Level 4 (3,180 points)
Currently Being Moderated
Feb 17, 2013 9:24 AM (in response to Martin Gallo)

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 (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 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

• Level 7 (27,380 points)
Currently Being Moderated
Feb 17, 2013 9:50 AM (in response to Martin Gallo)

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

• Level 4 (3,180 points)
Currently Being Moderated
Feb 17, 2013 10:03 AM (in response to Jerrold Green1)

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

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.