Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Please?
Bob Phillips wrote:
If always that format =MID(A1,FIND("C",A")+1,1) This gives a #VALUE! prompt. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |