Is there a formula to extract numbers only from a cell?
My data is like this:
SCORPION ATR ALL TERRAIN 116H OWL
P ZERO RUNFLAT 96Y BLK OE
SCORPION VERDE ALL SEASON ECOIMPACT 105V BLK XL OE
I want to get:
118
96
105
also i want to get the letter at the right of each value
H
Y
V
Thanks in advance
MacBook Pro 15″, macOS 10.15
One way is to do something like this:
In B2, filled down:
=REGEX.EXTRACT(A2,"\d+",1)
In C2, filled down:
=LEFT(TEXTAFTER(A2,B2),1)
This extracts the numbers as "text." If you want them as values on which you can perform arithmetic then you can simply multiply by 1, like this:
=REGEX.EXTRACT(A2,"\d+",1)*1
SG
One way is to do something like this:
In B2, filled down:
=REGEX.EXTRACT(A2,"\d+",1)
In C2, filled down:
=LEFT(TEXTAFTER(A2,B2),1)
This extracts the numbers as "text." If you want them as values on which you can perform arithmetic then you can simply multiply by 1, like this:
=REGEX.EXTRACT(A2,"\d+",1)*1
SG
jaxjason wrote:
its worth noting that regular expressions is a new feather to iWork.
And it is amazing how many times we've used those new functions since their recent introduction. Operations on text are so much simpler now.
its worth noting that regular expressions is a new feather to iWork.
Here is a website that will help explain how regular expressions work in general.
Thank you! It worked for what I wanted!
Extract Numbers Only From Cell
