Formula to gather numbers before a dash

[aerosmith]

Good Afternoon

Im wondering if anyone can help me with a formula that will extractnumbers before a dash,

I have a column that has entries like the following

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 192pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=256 height=17>From Store: 36 - Halifax Monastery</TD></TR></TBODY></TABLE>

The number could be 2 or 4 in length .

there is always a dash following the number,

I need a way to Extract only the Number

Is this possible?

Thanks,

 

[Jonmo1]

Welcome to the board...

Try

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

 

[aerosmith]

when i try this, it gives me an error, and wont get the number.

Welcome to the board...

Try

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

 

[Haseeb Avarakkan]

Hello, Try

=LOOKUP(1E+100,CHOOSE({1,2},0,LOOKUP(1E+100,--RIGHT(LEFT(A1,FIND("-",A1)-1),{2,3,4,5,6}))))

If couldn't find any number before the "-" will show 0

 

[Marcelo Branco]

Maybe this

=TRIM(MID(A1,FIND(":",A1)+1,FIND("-",A1)-FIND(":",A1)-1))

HTH

M.

 

[aerosmith]

im trying to do something similar

instead of the - in looking up to the right of the colon: and trying to get a value similar to this

From Store : 4914 Halifax Monastery

this worked very well for the lookup of the -

=LOOKUP(1E+100,CHOOSE({1,2},0,LOOKUP(1E+100,--RIGHT(LEFT(A1,FIND("-",A1)-1),{2,3,4,5,6}))))

but now if i try to change the - to a : and the Left to a right, it doesnt work properly.

 

[T. Valko]

im trying to do something similar

instead of the - in looking up to the right of the colon: and trying to get a value similar to this

From Store : 4914 Halifax Monastery

this worked very well for the lookup of the -

=LOOKUP(1E+100,CHOOSE({1,2},0,LOOKUP(1E+100,--RIGHT(LEFT(A1,FIND("-",A1)-1),{2,3,4,5,6}))))

but now if i try to change the - to a : and the Left to a right, it doesnt work properly.

Try this...

=LOOKUP(1E100,CHOOSE({1,2},0,LOOKUP(1E100,--MID(A2,FIND(":",A2)+2,{1,2,3,4,5,6}))))

That'll work for numbers up to 6 digits. If the number might be longer then just expand this array as needed:

{1,2,3,4,5,6}