Pulling the number from the text

[smalik]

What is the best way to pull a number from the text?

Here is an example:
4+ years of experience ....
10+ years of experience ....

All of my cells have the similar text. I thought I could just use the left (A1,1) but then I came to an issue when the text says, "10+ years of experience ...

I tried Left (A1,2) formula but then the years with single digit pulls the "+"

Is there a way to just pull the number whether it is a single digit or two digits?

Any help is greatly appreciated.

Best,

 

[Fluff]

Will the number always have a + sign after it?

 

[jtakw]

Hi,

If there's always a "+", use B formula, otherwise, use C formula:

Book3.xlsx
	A	B	C
1	4+ years of experience ....	4	4
2	10+ years of experience ....	10	10
3	15 years of…		15
Sheet1055

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,FIND("+",A1)-1)+0
C1:C3	C1	=LEFT(A1,IFERROR(FIND("+",A1),FIND(" ",A1))-1)+0

 

[Fluff]

Another option if the + sign is always there is

=LEFT(A2,FIND("+",A2)-1)+0

which will convert the number into a real number

 

[smalik]

Hi,

If there's always a "+", use B formula, otherwise, use C formula:

Book3.xlsx
	A	B	C
1	4+ years of experience ....	4	4
2	10+ years of experience ....	10	10
3	15 years of…		15
Sheet1055

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,FIND("+",A1)-1)+0
C1:C3	C1	=LEFT(A1,IFERROR(FIND("+",A1),FIND(" ",A1))-1)+0

Awesome .... This works. Thanks.

 

[jtakw]

You're welcome, thanks for the feedback.