return a text that is before "/"

  • Thread starter Thread starter Piotr (Peter)
  • Start date Start date
P

Piotr (Peter)

Dear All,

I have tried MID, FIND, LEFT function but to no avail as the length of a
string can vary:

97/2008
98/2008
99/2008
108/2008
109/2008
495/2008
496/2008
CORRECTION 3/2008
498/2008


What I need to achieve is to have a function that will return everything
before "/" and without any "space" at the end: e.g. it will return 498 only
without emtpy space (something like TRIM).

Any help appreciated.
 
a function that will return everything before "/" ..
For the above, this will return it as text/text nums
In B1, copied down: =LEFT(A1,SEARCH("/",A1)-1)

If "CORRECTION" is the only text that might be embedded within, and you need
it returned as real numbers (ie nums before the "/"), try in B1, copied down:
=LEFT(SUBSTITUTE(A1,"CORRECTION",""),SEARCH("/",SUBSTITUTE(A1,"CORRECTION",""))-1)+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
Thanks a lot Max. That did it:))

Max said:
For the above, this will return it as text/text nums
In B1, copied down: =LEFT(A1,SEARCH("/",A1)-1)

If "CORRECTION" is the only text that might be embedded within, and you need
it returned as real numbers (ie nums before the "/"), try in B1, copied down:
=LEFT(SUBSTITUTE(A1,"CORRECTION",""),SEARCH("/",SUBSTITUTE(A1,"CORRECTION",""))-1)+0
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
Click to expand...
 
Back
Top