formula for pulling only numbers but not text from another cell

[Guest]

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

 

[Guest]

In cell B2 type

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

In Cell B3 type

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

 

[RagDyeR]

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&"0123456789")),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&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))),"")

--

HTH,

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

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

 

[Guest]

Try these:

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

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

HTH,
Elkar

 

[RagDyeR]

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


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&"0123456789")),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&"0123456789")),ROW(INDIRECT("1:"&LEN(A2))))),"")

--

HTH,

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

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

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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