Formula Please?
 

I have the following data in cell A1.

4K (C4)

I need to extract both numerals.

By placing the following formula in another cell extracts the first 4.

=LEFT(A1,FIND("K",A1,1)-1)

What is the formula for removing (C) and placing the 2nd 4 in another cell?

TIA

Formula Please?
 

find )
--
Don Guillett
SalesAid Software

"Saxman" wrote in message
...
I have the following data in cell A1.

4K (C4)

I need to extract both numerals.

By placing the following formula in another cell extracts the first 4.

=LEFT(A1,FIND("K",A1,1)-1)

What is the formula for removing (C) and placing the 2nd 4 in another
cell?

TIA

Formula Please?
 

If always that format

=MID(A1,FIND("C",A")+1,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Saxman" wrote in message
...
I have the following data in cell A1.

4K (C4)

I need to extract both numerals.

By placing the following formula in another cell extracts the first 4.

=LEFT(A1,FIND("K",A1,1)-1)

What is the formula for removing (C) and placing the 2nd 4 in another
cell?

TIA

Formula Please?
 

Bob Phillips wrote:
If always that format

=MID(A1,FIND("C",A")+1,1)

This gives a #VALUE! prompt.

Formula Please?
 

Bob Phillips wrote:
If always that format

=MID(A1,FIND("C",A")+1,1)

Excel corrects this to

=MID(A1,FIND("C","A")+1,1)

Formula Please?
 

You use the word 'numeral' suggesting a single digit in each case.
But then you FIND K rather than using =LEFT(A1,1) to get the first 4.
So I assume we could have, for example, 15K (C56)
Try this
=LEFT(MID(A1,FIND("(",A1)+2,2),FIND(")",A1)-FIND("(",A1)-2)
By the way if you want the results to be numeric precede each formula
with -- (a pair of unitary negations)
--=LEFT(MID(A1,FIND("(",A1)+2,2),FIND(")",A1)-FIND("(",A1)-2)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Saxman" wrote in message
...
I have the following data in cell A1.

4K (C4)

I need to extract both numerals.

By placing the following formula in another cell extracts the first 4.

=LEFT(A1,FIND("K",A1,1)-1)

What is the formula for removing (C) and placing the 2nd 4 in another
cell?

TIA

Formula Please?
 

Clearly Bob meant to type =MID(A1,FIND("C",A1)+1,1)

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Saxman" wrote in message
...
Bob Phillips wrote:
If always that format

=MID(A1,FIND("C",A")+1,1)

Excel corrects this to

=MID(A1,FIND("C","A")+1,1)

Formula Please?
 

If they're *always* single digits,

Extract the first 4 with:
=Left(A1)

And the second 4 with:
=Left(Right(A1,2))
--

HTH,

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

"Saxman" wrote in message
...
I have the following data in cell A1.

4K (C4)

I need to extract both numerals.

By placing the following formula in another cell extracts the first 4.

=LEFT(A1,FIND("K",A1,1)-1)

What is the formula for removing (C) and placing the 2nd 4 in another cell?

TIA

Formula Please?
 

Bernard Liengme wrote:

--=LEFT(MID(A1,FIND("(",A1)+2,2),FIND(")",A1)-FIND("(",A1)-2)

This works fine.

Thank you so much to all for your learned advice.

Formula Please?
 

RagDyeR wrote:
If they're *always* single digits,

They are.

Extract the first 4 with:
=Left(A1)

And the second 4 with:
=Left(Right(A1,2))

Both work fine.

Thanks again.

Formula Please?
 

Thanks for the feed-back.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Saxman" wrote in message
...
RagDyeR wrote:
If they're *always* single digits,

They are.

Extract the first 4 with:
=Left(A1)

And the second 4 with:
=Left(Right(A1,2))

Both work fine.

Thanks again.