formula for separating parts of cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
 
..... sorry ... remove % ...

E13:

=TRIM(MID($C$13,FIND("(",$C$13)+1,FIND(")",$C$13)-FIND("(",$C$13)-2))
 
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.
 
currently in cell E5 = MACPAC VER 10 PGMR *Keep Profile* Clarksville

how do i extract the last word in this cell?
Click to expand...

Try this:

=TRIM(RIGHT(SUBSTITUTE(E5," ",REPT(" ",99)),99))

--ron
 
that worked wonderfully.

One more time how do I extract the first and second word only?
 
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
 
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".
 
Back
Top