Extracting the first number only in the following string

[manzier]

Hi all,

I have text like the following:

<tbody></tbody>

Num: 73, Percentage: 20.39%

Num: 260, Percentage: 72.63%

Num: 2, Percentage: 1.43%

<tbody>
</tbody>

How can I write one formula to just pull the first number only? Taking into account that the number can be anywhere from 1-3 digits long?

I tried using a simple LEFT and MID formula but I couldn't account for the different number sizes

Any suggestions? Thanks!

 

[bbott]

How about:

=TRIM(MID(A1,FIND(" ",A1),FIND(",",A1)-FIND(" ",A1)))

 

[Jonmo1]

Assuming the number is ALWAYS preceded with the text "Num: "
And the number is ALWAYS 1 to 3 digits, never 4 or more.

=SUBSTITUTE(MID(A1,6,3),",","")+0

 

[Mike LH]

Hi,

Try this

=LOOKUP(1E+100,--MID(A1,SMALL(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"),1),ROW(INDIRECT("1:"&LEN(A1)))))

 

[manzier]

Actually I got it. Used this formula:

=TRIM(LEFT(SUBSTITUTE(REPLACE(C2,1,FIND("@",SUBSTITUTE(C2,":","@",1)),""),",",REPT(" ",100),1),100))

Thanks to Richard Schollar in here

 

[manzier]

That works too, thanks MikeLH!

 

[manzier]

Thanks Jonmo1 that works as well