ignore text in formula
 

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

ignore text in formula
 

On Dec 29, 4:35*pm, jatman wrote:
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

ignore text in formula
 

Since the format is *always*

<number<space<text

you can use

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

In article ,
jatman wrote:

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

ignore text in formula
 

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!
================================================== ===



"jatman" wrote in message
...
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

ignore text in formula
 

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

number<spacetext

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

--
Biff
Microsoft Excel MVP


"jatman" wrote in message
...
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

ignore text in formula
 

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

Regards,

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

"RagDyeR" wrote in message
...
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!
================================================== ===



"jatman" wrote in message
...
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

ignore text in formula
 

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&"012345 6789")),ROW(INDIRECT("1:256"))))
--

HTH,

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


"RagDyeR" wrote in message
...
Of course, the unary is superfluous in my examples.
--

Regards,

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

"RagDyeR" wrote in message
...
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!
================================================== ===



"jatman" wrote in message
...
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

ignore text in formula
 

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


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44840

ignore text in formula
 

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

"jatman" wrote in message
...
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

ignore text in formula
 

=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.



In article ,
"Shane Devenshire" wrote:

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

ignore text in formula
 

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

"JE McGimpsey" wrote in message
...
=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.

In article ,
"Shane Devenshire" wrote:

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

ignore text in formula
 

That would make more sense, yeah.

In article ,
"David Biddulph" <groups [at] biddulph.org.uk wrote:

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