Extract using MID function ?
 

I have cells with numbers of various character counts
seperated by an "x".

eg.
2x1.5
48x3

I can find the "x" position with the MID function.

Now how do I extract
ALL numbers to the right of the "x"
or
ALL numbers to the left of the "x" ?

Thank you,
Emory

Everything before the x:
=--LEFT(A1,SEARCH("x",A1,1)-1)

Everything after the x:
=--MID(A1,SEARCH("x",A1,1)+1,255)

=left() and =mid() both return strings. If you want to use those values as
numbers, you can convert them by using -- in front of the expression.

=search() and =find() are very similar functions. =Find() is case sensitive.
=search() is not.

Emory Richter wrote:

I have cells with numbers of various character counts
seperated by an "x".

eg.
2x1.5
48x3

I can find the "x" position with the MID function.

Now how do I extract
ALL numbers to the right of the "x"
or
ALL numbers to the left of the "x" ?

Thank you,
Emory

--

Dave Peterson

Numbers to the left of the"x":
=LEFT(A1,SEARCH("x",A1)-1)*1

Numbers to the right of the "x":
=MID(A1,SEARCH("x",A1)+1,15)*1

I hope that helps.

--
Regards,
Ron

For RIGHT of the "X":
=RIGHT(A1,LEN(A1)-SEARCH("X",A1))

For LEFT of the "X":
=LEFT(A1,SEARCH("x",A1)-1)

And if you want *real* numbers:
=--RIGHT(A1,LEN(A1)-SEARCH("X",A1))
=--LEFT(A1,SEARCH("x",A1)-1)
--

HTH,

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

"Emory Richter" wrote in message
om...
I have cells with numbers of various character counts
seperated by an "x".

eg.
2x1.5
48x3

I can find the "x" position with the MID function.

Now how do I extract
ALL numbers to the right of the "x"
or
ALL numbers to the left of the "x" ?

Thank you,
Emory

Dave
Thanks for the methods.
One thing,
In SEARCH("x",A1,1)
I understand that the "x" is the thing_to_search
and A1 is the source.
What is the third item, the 1?

In article , ec35720
@netscapeXSPAM.com says...
Everything before the x:
=--LEFT(A1,SEARCH("x",A1,1)-1)

Everything after the x:
=--MID(A1,SEARCH("x",A1,1)+1,255)

=left() and =mid() both return strings. If you want to use those values as
numbers, you can convert them by using -- in front of the expression.

=search() and =find() are very similar functions. =Find() is case sensitive.
=search() is not.

Emory Richter wrote:

I have cells with numbers of various character counts
seperated by an "x".

eg.
2x1.5
48x3

I can find the "x" position with the MID function.

Now how do I extract
ALL numbers to the right of the "x"
or
ALL numbers to the left of the "x" ?

Thank you,
Emory

Ron
Thanks for the methods.
One thing,
at the end of the function
you seem to multiply by 1
[ *1 ].
What is the purpose?

Emory


In article ,
says...
Numbers to the left of the"x":
=LEFT(A1,SEARCH("x",A1)-1)*1

Numbers to the right of the "x":
=MID(A1,SEARCH("x",A1)+1,15)*1

I hope that helps.

In article ,
says...
For RIGHT of the "X":
=RIGHT(A1,LEN(A1)-SEARCH("X",A1))

For LEFT of the "X":
=LEFT(A1,SEARCH("x",A1)-1)

And if you want *real* numbers:
=--RIGHT(A1,LEN(A1)-SEARCH("X",A1))
=--LEFT(A1,SEARCH("x",A1)-1)

ragdyer
Thanks for the alternate possibilities.
Emory

The functions we're using return text strings. To make Excel convert the text
to numbers, we need to use an arithmetic operator. You'll see, at various
times, we multiply the result by 1 OR we'll preceed the result with -- (two
minus signs which offset each other). Also, we only do that if the expected
result will be:
Numeric text: to return a number
or
Boolean (true/false): to convert TRUE to 1, FALSE to 0 (zero)

Otherwise, an error is returned. (There is no such thing as negative "Tree")

Does that help?
--
Regards,
Ron

In article ,
says...
The functions we're using return text strings. To make Excel convert the text
to numbers, we need to use an arithmetic operator. You'll see, at various
times, we multiply the result by 1 OR we'll preceed the result with -- (two
minus signs which offset each other). Also, we only do that if the expected
result will be:
Numeric text: to return a number
or
Boolean (true/false): to convert TRUE to 1, FALSE to 0 (zero)

Otherwise, an error is returned. (There is no such thing as negative "Tree")

Does that help?

Yup, that helps.
I think I understand the whole thing now.
Thanks,
Emory

It's the starting position for the search. The default is 1, so I didn't have
to use it.

But if I wanted to ignore the first 2 characters, I could:
=SEARCH("x",A1,3)
so if A1 contained: xxasdfxqwer
that formula would return: 7

Excel's help is a nice spot to look for these kinds of questions <bg.


Emory Richter wrote:

Dave
Thanks for the methods.
One thing,
In SEARCH("x",A1,1)
I understand that the "x" is the thing_to_search
and A1 is the source.
What is the third item, the 1?

In article , ec35720
@netscapeXSPAM.com says...
Everything before the x:
=--LEFT(A1,SEARCH("x",A1,1)-1)

Everything after the x:
=--MID(A1,SEARCH("x",A1,1)+1,255)

=left() and =mid() both return strings. If you want to use those values as
numbers, you can convert them by using -- in front of the expression.

=search() and =find() are very similar functions. =Find() is case sensitive.
=search() is not.

Emory Richter wrote:

I have cells with numbers of various character counts
seperated by an "x".

eg.
2x1.5
48x3

I can find the "x" position with the MID function.

Now how do I extract
ALL numbers to the right of the "x"
or
ALL numbers to the left of the "x" ?

Thank you,
Emory



--

Dave Peterson

In article , ec35720
@netscapeXSPAM.com says...
t's the starting position for the search. The default is 1, so I didn't have
to use it.

But if I wanted to ignore the first 2 characters, I could:
=SEARCH("x",A1,3)
so if A1 contained: xxasdfxqwer
that formula would return: 7

Excel's help is a nice spot to look for these kinds of questions <bg.

Dave thanks for taking the time to write out the explaination.
I am so used to looking in my old book
I never thought of Excel Help.
But the info is there.

Emory