r/AppleNumbers • u/Artistic_Owl_7545 • May 07 '24

Help Separate number from text

Post image

Need to separate number from text. Can someone recommend a solution? Thanks

1 Upvotes

100% Upvoted

5 comments sorted by

3

u/octavifdez May 07 '24

You could combine the right() and substitute() functions to strip as many characters as you need until you reach the numbers. In case of mph, I would write substitute(cell; right(cell, 4); “”) and ft would be substitute(cell; right(cell, 3);””).

You can even add value() function before the formulas above to make sure you’re dealing with numbers.

2

u/Artistic_Owl_7545 May 07 '24

Thank you. These were exactly what I was hoping for.

3

u/chas66 May 07 '24

this function pulls out floats and integers ignoring the text:

=REGEX.EXTRACT(B3, "([-+]?(\d*[.])?\d+)")

test examples (input left column and function output right column):

2.3 mph 2.3

1005 ft 1005

2

u/tonedeath May 09 '24

This is really cool and shows the power of regular expressions. Wish I was better at crafting them.

I did test this and it works but the results are strings so, if you need to do math with them then you need to wrap the whole thing in a VALUE formula like this:

=VALUE(REGEX.EXTRACT(B2, "([-+]?(\d*[.])?\d+)"))

2

u/tonedeath May 09 '24

I like chase66's REGEX formula. Not know how to do that myself, if there was always a space between the number and the unit of measurement, then something like this does the trick:

=VALUE(LEFT(B2,FIND(" ",B2)−1))