| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula for separating parts of cells
Current text in C13: Down 174.69 (1.25%)
Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
In Cell B1: =FIND(" ",C13,1) find the first blank in your string
In Cell C1: =FIND(" ",C13,B1+1) find the next blank In Cell D13: =VALUE(MID(C13,B1+1,C1-B1-1)) extract between the two blanks In Cell E1: =FIND("(",C13) In Cell F1: =FIND(")",C13) In Cell E13: =VALUE(MID(C13,E1+1,F1-E1-1)) If you want to avoid the intermediate cells, feel free to replace their value with formulas in D13 and E13, something like: D13: = =VALUE(MID(C13,FIND(" ",C13,1)+1,FIND(" ",C13,FIND(" ",C13,1)+1)-FIND(" ",C13,1)-1)), but it is hardly undersandable and make sheet maintenance more difficult, in my humble opinion. Stephane. "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
D13:
=TRIM(MID($C$13,FIND(" ",$C$13),FIND("(",$C$13)-FIND(" ",$C$13))) D13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-1)) HTH "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
..... sorry ... remove % ...
E13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2)) "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
AWESOME!!! THANKS!
"Toppers" wrote: .... sorry ... remove % ... E13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2)) "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
One more...
Desred text for F13: Down "Toppers" wrote: .... sorry ... remove % ... E13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2)) "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
F13:
=TRIM(MID(C13,1,FIND(" ",C13)-1)) "Jamie" wrote: One more... Desred text for F13: Down "Toppers" wrote: .... sorry ... remove % ... E13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2)) "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
One more please.
Current text Sheet 1 B13: 6,513,386 Desired text in Sheet 2 E4: 6,513. "Toppers" wrote: F13: =TRIM(MID(C13,1,FIND(" ",C13)-1)) "Jamie" wrote: One more... Desred text for F13: Down "Toppers" wrote: .... sorry ... remove % ... E13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2)) "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
=Left(B13,LEN(B13)-4)
to remove last 3 digits +"," "Jamie" wrote: One more please. Current text Sheet 1 B13: 6,513,386 Desired text in Sheet 2 E4: 6,513. "Toppers" wrote: F13: =TRIM(MID(C13,1,FIND(" ",C13)-1)) "Jamie" wrote: One more... Desred text for F13: Down "Toppers" wrote: .... sorry ... remove % ... E13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2)) "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
When I use that formula I get: 6513
I need the comma and the decimal. 6,513. "Toppers" wrote: =Left(B13,LEN(B13)-4) to remove last 3 digits +"," "Jamie" wrote: One more please. Current text Sheet 1 B13: 6,513,386 Desired text in Sheet 2 E4: 6,513. "Toppers" wrote: F13: =TRIM(MID(C13,1,FIND(" ",C13)-1)) "Jamie" wrote: One more... Desred text for F13: Down "Toppers" wrote: .... sorry ... remove % ... E13: =TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2)) "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville
how do i extract the last word in this cell? the legnth could vary which is why I tried using this formula. In fact I put the formula in my spreadsheet to see if I could it to extract anything and i got an error message. So now I just want to know how to alter the formula to extract either the last or third from the last word. in my spreadsheet: in cell B1 ==FIND(" ",E5,1) in cell C1 =FIND(" ",E5,B1+1) in cell D1 =VALUE(MID(E5,B1+1,C1-B1-1)) This is where I stopped because cell D1 returns error message #value. "Stephane Quenson" wrote: In Cell B1: =FIND(" ",C13,1) find the first blank in your string In Cell C1: =FIND(" ",C13,B1+1) find the next blank In Cell D13: =VALUE(MID(C13,B1+1,C1-B1-1)) extract between the two blanks In Cell E1: =FIND("(",C13) In Cell F1: =FIND(")",C13) In Cell E13: =VALUE(MID(C13,E1+1,F1-E1-1)) If you want to avoid the intermediate cells, feel free to replace their value with formulas in D13 and E13, something like: D13: = =VALUE(MID(C13,FIND(" ",C13,1)+1,FIND(" ",C13,FIND(" ",C13,1)+1)-FIND(" ",C13,1)-1)), but it is hardly undersandable and make sheet maintenance more difficult, in my humble opinion. Stephane. "Jamie" wrote: Current text in C13: Down 174.69 (1.25%) Desired Text in D13: 174.69 Desired Text in E13: 1.25 What formulas can I put in D13 and E13 to get the desired text? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
On Wed, 9 Sep 2009 05:42:22 -0700, confused!!
wrote: currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville how do i extract the last word in this cell? Try this: =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99)) --ron |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
that worked wonderfully.
One more time how do I extract the first and second word only? "Ron Rosenfeld" wrote: On Wed, 9 Sep 2009 05:42:22 -0700, confused!! wrote: currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville how do i extract the last word in this cell? Try this: =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99)) --ron |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
Hi,
For the first word, try this =left(E5,search(" ",E5)-1). This assumes that there will be a space after the first word -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "confused!!" wrote in message ... that worked wonderfully. One more time how do I extract the first and second word only? "Ron Rosenfeld" wrote: On Wed, 9 Sep 2009 05:42:22 -0700, confused!! wrote: currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville how do i extract the last word in this cell? Try this: =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99)) --ron |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
How do i extract the second word only
"Ashish Mathur" wrote: Hi, For the first word, try this =left(E5,search(" ",E5)-1). This assumes that there will be a space after the first word -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "confused!!" wrote in message ... that worked wonderfully. One more time how do I extract the first and second word only? "Ron Rosenfeld" wrote: On Wed, 9 Sep 2009 05:42:22 -0700, confused!! wrote: currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville how do i extract the last word in this cell? Try this: =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99)) --ron |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
also let's assume (i) there is no space (ii) there is a space after the word
"confused!!" wrote: How do i extract the second word only "Ashish Mathur" wrote: Hi, For the first word, try this =left(E5,search(" ",E5)-1). This assumes that there will be a space after the first word -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "confused!!" wrote in message ... that worked wonderfully. One more time how do I extract the first and second word only? "Ron Rosenfeld" wrote: On Wed, 9 Sep 2009 05:42:22 -0700, confused!! wrote: currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville how do i extract the last word in this cell? Try this: =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99)) --ron |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
For relatively short text strings...
=TRIM(MID(SUBSTITUTE(E5," ",REPT(" ",999)),999*4-998,999*2)) In the formula above, the "4" is first word you want, the "2" is the number of words you want. So this would return "PGMR *Keep". confused!! wrote: that worked wonderfully. One more time how do I extract the first and second word only? "Ron Rosenfeld" wrote: On Wed, 9 Sep 2009 05:42:22 -0700, confused!! wrote: currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville how do i extract the last word in this cell? Try this: =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99)) --ron |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
On Wed, 9 Sep 2009 06:13:15 -0700, confused!!
wrote: that worked wonderfully. One more time how do I extract the first and second word only? Do you mean together or separately? --ron |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula for separating parts of cells
thanks for explaining. The formula is exactly what i needed.
"Glenn" wrote: For relatively short text strings... =TRIM(MID(SUBSTITUTE(E5," ",REPT(" ",999)),999*4-998,999*2)) In the formula above, the "4" is first word you want, the "2" is the number of words you want. So this would return "PGMR *Keep". confused!! wrote: that worked wonderfully. One more time how do I extract the first and second word only? "Ron Rosenfeld" wrote: On Wed, 9 Sep 2009 05:42:22 -0700, confused!! wrote: currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville how do i extract the last word in this cell? Try this: =TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99)) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF in a range (and parts of cells) | Excel Worksheet Functions | |||
| separating cells with text | Excel Discussion (Misc queries) | |||
| separating text from cells | Excel Discussion (Misc queries) | |||
| Deleting Parts of Cells | Excel Discussion (Misc queries) | |||
| is there an easy way to move parts of cells around | Excel Discussion (Misc queries) | |||
Linear Mode
