ignore text in formula

[jatman]

i am using the following formula

=J17*H17

the problem is J17 is imported from another file, and that always contains a
number value along with some text value (ex. 5 CS). when the formula is
calcuated, it returns #VALUE!

can the formula be written so that it looks at the number value only and
ignores the text?

thank you,

jat

 

[barry houdini]

i am using the following formula

=J17*H17

the problem is J17 is imported from another file, and that always contains a
number value along with some text value (ex. 5 CS).  when the formula is
calcuated, it returns #VALUE!

can the formula be written so that it looks at the number value only and
ignores the text?

thank you,

jat

If J17 is alway number space text then try

=MID(J17,1,FIND(" ",J17&" ")-1)*H17

 

[JE McGimpsey]

Since the format is *always*

<number><space><text>

you can use

=LEFT(J17,FIND(" ",J17)-1) * K17

 

[RagDyeR]

You didn't elaborate on *all* the possibilities that may exist in J17.

This works for your example:

=--LEFT(J17)*H17

If maybe more then a single digit:

=--LEFT(J17,FIND(" ",J17)-1)*H17

Both suggestions assume a <space> between the leading number and the text.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



i am using the following formula

=J17*H17

the problem is J17 is imported from another file, and that always contains a
number value along with some text value (ex. 5 CS). when the formula is
calcuated, it returns #VALUE!

can the formula be written so that it looks at the number value only and
ignores the text?

thank you,

jat

 

[T. Valko]

As long as the entry *always* follows the this pattern:

number<space>text

=H17*LEFT(J17,FIND(" ",J17))

 

[RagDyeR]

Of course, the unary is superfluous in my examples.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

You didn't elaborate on *all* the possibilities that may exist in J17.

This works for your example:

=--LEFT(J17)*H17

If maybe more then a single digit:

=--LEFT(J17,FIND(" ",J17)-1)*H17

Both suggestions assume a <space> between the leading number and the text.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



i am using the following formula

=J17*H17

the problem is J17 is imported from another file, and that always contains a
number value along with some text value (ex. 5 CS). when the formula is
calcuated, it returns #VALUE!

can the formula be written so that it looks at the number value only and
ignores the text?

thank you,

jat

 

[RagDyeR]

If, on the other hand, numbers are possibly mixed within the text, this will
return the *first* set of numbers:

=H17*LOOKUP(99^99,--MID(J17,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J17&"0123456789")),ROW(INDIRECT("1:256"))))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Of course, the unary is superfluous in my examples.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

You didn't elaborate on *all* the possibilities that may exist in J17.

This works for your example:

=--LEFT(J17)*H17

If maybe more then a single digit:

=--LEFT(J17,FIND(" ",J17)-1)*H17

Both suggestions assume a <space> between the leading number and the text.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



i am using the following formula

=J17*H17

the problem is J17 is imported from another file, and that always contains a
number value along with some text value (ex. 5 CS). when the formula is
calcuated, it returns #VALUE!

can the formula be written so that it looks at the number value only and
ignores the text?

thank you,

jat

 

[JBeaucaire]

This isn't as short, but will work to draw out the numbers from the text
string regardless of where/how they exist in the cell. If the string is
in A1, the formula would be:

*=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

* 1234 DOG 1234 DODG214 214

 

[Shane Devenshire]

Hi,

If you maintain the pattern you show here - number first, space, text then

=LEFT(FIND(" ",J17))*H17

this simplifies to

=LEFT(J17)*H17

if the number is always a single digit

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[JE McGimpsey]

=LEFT(FIND(" ",J17))*H17

FIND(" ",J17) will return the *position* of the space, i.e. it
*always* returns 2 if a single digit followed by space and text.

 

[David Biddulph]

I think Shane perhaps intended to say
=LEFT(J17,FIND(" ",J17)-1)*H17

 

[JE McGimpsey]

That would make more sense, yeah.