Pulling the number from the text (Part 2)

[smalik]

Recently, I asked the following question:

How to pull the number from the following statements:
4+ years of experience ....
10+ years of experience ....

This was the example when the number was at the beginning of the text.

The following formula worked perfectly fine
=LEFT(A1,FIND("+",A1)-1)

Although majority of the cells have the number at the beginning, I am finding that the number could be in the middle of the string as well. How do I modify this formula so that I can capture the number irrespective of where it is. The number always comes with a + sign, e.g., 5+ or 10+

Any help is greatly appreciated.

Thanks.

 

[kweaver]

Is there only 1 number in the string? Here's one way but it uses INDIRECT.

Book3
	A	B
1	5+ years of experience	5
2	10+ years of experience	10
3	Experience of 15+ years	15
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=1*TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))

 

[kweaver]

In fooling around w/this I also found that a large percent of the time FLASH FILL works, too.

 

[JEC]

One more

=TRIM(MID(" "&A1,SEARCH("+"," "&A1)-2,2))+0

 

[Peter_SSs]

Is there only 1 number in the string? Here's one way but it uses INDIRECT.

But it could use SEQUENCE instead of INDIRECT, and CONCAT would make more sense than TEXTJOIN with "" as the delimiter.

=CONCAT(IFERROR(MID(A1,SEQUENCE(LEN(A1)),1)+0,""))+0

One more

=TRIM(MID(" "&A1,SEARCH("+"," "&A1)-2,2))+0

As I am sure you are aware, this limits the number to a maximum of two digits - which looks like it could be the case if they are all "years of experience".
TRIM would not be required though, and one of the " "& can be eliminated
FIND would suffice here too.

=MID(" "&A1,FIND("+",A1)-1,2)+0

Another option in case the number could be longer than 2 digits and/or contain decimals is shown in column B below.
Also, if you happen to have the TEXTAFTER function (not many will have it yet, but it is coming), column C offers a simpler alternative.

22 03 26.xlsm
	A	B	C
1	5+ years of experience	5	5
2	10+ years of experience	10	10
3	Experiences of 15+ years	15	15
4	Experiences of 7+ years	7	7
5	Experiences of 1.5+ years	1.5	1.5
6	Combined experience of 103+ years	103	103
Get Num

Cell Formulas
Range	Formula
B1:B6	B1	=RIGHT(SUBSTITUTE(LEFT(A1,FIND("+",A1)-1)," ",REPT(" ",20)),20)+0
C1:C6	C1	=TEXTAFTER(LEFT(" "&A1,FIND("+",A1))," ",-1)+0

 

[smalik]

Thanks everyone @Peter_SSs, @JEC, @kweaver, for helping me with this formula. This is an amazing group.

I tried all the options. Here is what I came up with.

• For some reason my Excel version did not recognize "Textafter' command. I received #NAME? error
• =RIGHT(SUBSTITUTE(LEFT(A1,FIND("+",A1)-1)," ",REPT(" ",20)),20)+0 -- This formula worked with all entries in my dataset
• =TRIM(MID(" "&A6,SEARCH("+"," "&A6)-2,2))+0 and =MID(" "&A6,FIND("+",A6)-1,2)+0 -- These two formulas gave me 0.5 as a result with "1.5+ years"
• =CONCAT(IFERROR(MID(A6,SEQUENCE(LEN(A6)),1)+0,""))+0 and =1*TEXTJOIN("",TRUE,IFERROR((MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)*1),"")) -- These two formulas gave me 15 as a result with "1.5+ years"

PS: I am using "Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20128) 64-bit "

 

[Peter_SSs]

Thanks everyone

You're welcome.

For some reason my Excel version did not recognize "Textafter' command. I received #NAME? error

That is not surprising as TEXTAFTER is a very new function & m/soft is just rolling it out to a small number of subscribers at a time.

In relation to some suggestions not working with numbers like 1.5, it is important to use your sample data to show what variety might be in your real data since we have no idea unless you do that.