return a text that is before "/"
 

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.

return a text that is before "/"
 

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
---
"Piotr (Peter)" wrote:
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.

return a text that is before "/"
 

Thanks a lot Max. That did it:))

"Max" wrote:

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
---
"Piotr (Peter)" wrote:
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.

return a text that is before "/"
 

Welcome, glad it did.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Piotr (Peter)" wrote in message
...
Thanks a lot Max. That did it:))