Difference between revisions of "Excel formulas"

From S23Wiki
Jump to: navigation, search
m (Reverted edits by Guadalupe633y (talk) to last revision by DrOwl)
Line 4: Line 4:
 
==Capture the last segment==
 
==Capture the last segment==
  
The first one is what I found on [[http://safepillstock.com/?a=46688 Mr Excel]]  
+
The first one is what I found on [[http://www.mrexcel.com/board2/viewtopic.php?t=138636&view=previous&sid=423a751097c698bb83afc77bdd14c2be Mr Excel]]  
  
 
The second is what I wanted =)
 
The second is what I wanted =)
Line 49: Line 49:
  
 
---------
 
---------
 +
 +
 +
[[Category:Computer]]
 +
[[Category:Software]]
 +
[[Category:Windows]]

Revision as of 10:33, 17 November 2011

a collection of "forulars" i have used in Excel


Capture the last segment

The first one is what I found on [Mr Excel]

The second is what I wanted =)

Very nice logic I thourght.


=REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),"") - extracts the last 2 space seperated segments - ie "moo foo" from "who are moo foo"


=REPLACE(H12,1,SEARCH("@",SUBSTITUTE(H12,"-","@",LEN(H12)-LEN(SUBSTITUTE(H12,"-","")))),"") - extracts the last minus seperated segment - ie "foo" from "who-are-moo-foo"



Quick dirt explanation (using above example)

[1]

LEN(H12)-LEN(SUBSTITUTE(H12,"-",""))

"Length of the target cell" - "Length of the target cell with out the seperator"

Len "who-are-moo-foo" = 15 - len "whoaremoofoo" = 12

= 3

[2]

SUBSTITUTE(H12,"-","@",[1])

Sub the [1]th "@" for a "-" in target cell.

Substitute the 3rd - in "who-are-moo-foo" with @

= "who-are-moo@foo"

[3]

REPLACE(H12,1,SEARCH("@",[2]),"")

Replace from char 1 to the "@" for string "who-are-moo@foo"

= "foo"