I need a formula to pull only numbers and one to pull oinly letters from a
cell and put them in another cell.
Scenario:
Worksheet A, Cell A2= (7) apples
I want: Worksheet B, Cell B2 to = (7)
Worksheet B, Cell B3 to = apples

In cell B2 type

=LEFT(A2,LEN(A2)-FIND(" ",A2,1))

In Cell B3 type

=RIGHT(A2,LEN(A2)-FIND(" ",A2,1))

"Jamie" wrote:

I need a formula to pull only numbers and one to pull oinly letters from a
cell and put them in another cell.
Scenario:
Worksheet A, Cell A2= (7) apples
I want: Worksheet B, Cell B2 to = (7)
Worksheet B, Cell B3 to = apples

This will return *only* the *first* contiguous set of numbers (*no* parens).

=LOOKUP(99^99,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789")),ROW(INDIRECT("1:"&LEN(A2)))))

This subtracts that *first* set of numbers, and returns what's left over:

=SUBSTITUTE(A2,LOOKUP(99^99,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789")),ROW(INDIRECT("1:"&LEN(A2))))),"")

--

HTH,

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

"Jamie" wrote in message
...
I need a formula to pull only numbers and one to pull oinly letters from a
cell and put them in another cell.
Scenario:
Worksheet A, Cell A2= (7) apples
I want: Worksheet B, Cell B2 to = (7)
Worksheet B, Cell B3 to = apples

Try these:

=LEFT('Worksheet A'!A2,FIND(")",'Worksheet A'!A2))

=MID('Worksheet A'!A2,FIND(")",'Worksheet A'!A2)+2,999)

HTH,
Elkar


"Jamie" wrote:

I need a formula to pull only numbers and one to pull oinly letters from a
cell and put them in another cell.
Scenario:
Worksheet A, Cell A2= (7) apples
I want: Worksheet B, Cell B2 to = (7)
Worksheet B, Cell B3 to = apples

This would subtract the contents of the cell where you entered the first
formula.
Makes the second formula a little shorter.<g

=SUBSTITUTE(A2,B2,"")
--

HTH,

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


"RagDyeR" wrote in message
...
This will return *only* the *first* contiguous set of numbers (*no* parens).

=LOOKUP(99^99,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789")),ROW(INDIRECT("1:"&LEN(A2)))))

This subtracts that *first* set of numbers, and returns what's left over:

=SUBSTITUTE(A2,LOOKUP(99^99,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789")),ROW(INDIRECT("1:"&LEN(A2))))),"")

--

HTH,

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

"Jamie" wrote in message
...
I need a formula to pull only numbers and one to pull oinly letters from a
cell and put them in another cell.
Scenario:
Worksheet A, Cell A2= (7) apples
I want: Worksheet B, Cell B2 to = (7)
Worksheet B, Cell B3 to = apples

OK - I apologize - the example I gave you was just an example and not exactly
what I need. I tried to follow your formula as a template for my specific
need and had trouble, Let me explain exactly what my situation is:
Scenario: Sheet 2, Cell B50= Change0.320
I want: Sheet 1, Cell C27 to = 0.320
Worksheet B, Cell D27 to = Change

"AKphidelt" wrote:

In cell B2 type

=LEFT(A2,LEN(A2)-FIND(" ",A2,1))

In Cell B3 type

=RIGHT(A2,LEN(A2)-FIND(" ",A2,1))

"Jamie" wrote:

I need a formula to pull only numbers and one to pull oinly letters from a
cell and put them in another cell.
Scenario:
Worksheet A, Cell A2= (7) apples
I want: Worksheet B, Cell B2 to = (7)
Worksheet B, Cell B3 to = apples

On Thu, 17 May 2007 11:06:00 -0700, Jamie
wrote:

OK - I apologize - the example I gave you was just an example and not exactly
what I need. I tried to follow your formula as a template for my specific
need and had trouble, Let me explain exactly what my situation is:
Scenario: Sheet 2, Cell B50= Change0.320
I want: Sheet 1, Cell C27 to = 0.320
Worksheet B, Cell D27 to = Change


And what was the problem with RagDyer's suggestion?

=LOOKUP(99^99,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))


--ron

I hadn't seen that post when I posted again. I'm about to try it. Keep your
pants on.

"Ron Rosenfeld" wrote:

On Thu, 17 May 2007 11:06:00 -0700, Jamie
wrote:

OK - I apologize - the example I gave you was just an example and not exactly
what I need. I tried to follow your formula as a template for my specific
need and had trouble, Let me explain exactly what my situation is:
Scenario: Sheet 2, Cell B50= Change0.320
I want: Sheet 1, Cell C27 to = 0.320
Worksheet B, Cell D27 to = Change


And what was the problem with RagDyer's suggestion?

=LOOKUP(99^99,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))


--ron