Results 1 to 11 of 11

20080830, 06:57 #1
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
extracting characters from a cell (XP 2003)
I would like to record the characters from a cell before a "/" or a " ". I am attaching a sample in col A and what the results shoul look like in the first 5 rows in col B. But I cannot get the formula. Any advice.
Thanks

20080830, 07:15 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: extracting characters from a cell (XP 2003)
Enter this formula in B1:
<code>
=IF(NOT(ISERROR(FIND("/",A1))),LEFT(A1,FIND("/",A1)1),IF(NOT(ISERROR(FIND(" ",A1))),LEFT(A1,FIND(" ",A1)1),""))
</code>
and fill down as far as needed

20080830, 07:22 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: extracting characters from a cell (XP 2003)
If the characters will always be numbers and you want the numbers and not text, you can modify Hans' formula to:
=VALUE(IF(NOT(ISERROR(FIND("/",A1))),LEFT(A1,FIND("/",A1)1),IF(NOT(ISERROR(FIND(" ",A1))),LEFT(A1,FIND(" ",A1)1),"")))
If you want the numbers to be numbers and the text to be text you could use the rather long:
=IF(ISNUMBER(VALUE(IF(NOT(ISERROR(FIND("/",A1))),LEFT(A1,FIND("/",A1)1),IF(NOT(ISERROR(FIND(" ",A1))),LEFT(A1,FIND(" ",A1)1),"")))),VALUE(IF(NOT(ISERROR(FIND("/",A1))),LEFT(A1,FIND("/",A1)1),IF(NOT(ISERROR(FIND(" ",A1))),LEFT(A1,FIND(" ",A1)1),""))),IF(NOT(ISERROR(FIND("/",A1))),LEFT(A1,FIND("/",A1)1),IF(NOT(ISERROR(FIND(" ",A1))),LEFT(A1,FIND(" ",A1)1),"")))
Steve

20080830, 09:00 #4
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: extracting characters from a cell (XP 2003)
thank you

20080830, 09:00 #5
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: extracting characters from a cell (XP 2003)
thank you

20080830, 16:57 #6
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: extracting characters from a cell (XP 2003)
Try this formula….
=LOOKUP(1,(""&LEFT(A1,ROW($1:$1024))))
Regards
Bosco

20080830, 20:07 #7
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: extracting characters from a cell (XP 2003)
<img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>Works alright on my Excel 2003.....just figuring why the formula does it though!
Jerry

20080830, 20:37 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: extracting characters from a cell (XP 2003)
I must be missing something  when I try your formula, it extracts the first character of the cells in column A instead of the values indicated by Martin.
Added: oops, sorry, that's because my system uses a comma as decimal separator instead of a point.

20080830, 20:45 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: extracting characters from a cell (XP 2003)
Your remark prompted me to rethink  it didn't work for me because I use a comma as decimal separator.
Concatenating with "" makes the number part of the cell values negative.
Taking LEFT with $1:$1024 characters produces an array with the first character, the first 2 characters, the first 3 characters etc. (1024 is just a number larger than the length of the longest entry, you could use 20 instead).
 tries to convert this to a number, resulting in #VALUE if it fails. For example, for A1 you get
3, 3, 3.5, #VALUE, #VALUE, ...
LOOKUP tries to find the number 1, but since all values are negative, it returns the last number found in the array, which is precisely the one we want to return.
Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

20080830, 22:39 #10
 Join Date
 Aug 2004
 Posts
 123
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: extracting characters from a cell (XP 2003)
How the formula work
1] To extract the number from the left of the data eg
A1 : 3.5/1 COOP
A2 : 2.5/1 COOPSUB
The formula I used will be :
=LOOKUP(1,LEFT(A1,ROW($1:$1024)))
It worked well in most of the cases,
but, it worked fail in cell A3 ; 4/1 COOP
Excel consider 4/1 is a date system, and gave result in 39451 > ( 04/01/2008)
2] Then, a modified formula was used to solve the problem
=LOOKUP(1,(""&LEFT(A3,ROW($1:$1024))))
3] An explanation in below to show how the 2 formulas work
Data in A3 : 4/1 COOP
3.1] Cell B4 entered the formula no 1 :
=LOOKUP(1,LEFT(A3,ROW($1:$10)))
=LOOKUP(1,{"4";"4/";"4/1";"4/1 ";"4/1 C";"4/1 CO";"4/1 COO";"4/1 COOP";"4/1 COOP";"4/1 COOP"})
=LOOKUP(1,{4;#VALUE!;39451;39451;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VAL UE!})
=39451
=39451 > ( 04/01/2008)
3.2] Cell C4 entered the formula no 2 :
=LOOKUP(1,(""&LEFT(A3,ROW($1:$10))))
=LOOKUP(1,(""&{"4";"4/";"4/1";"4/1 ";"4/1 C";"4/1 CO";"4/1 COO";"4/1 COOP";"4/1 COOP";"4/1 COOP"}))
=LOOKUP(1,({"4";"4/";"4/1";"4/1 ";"4/1 C";"4/1 CO";"4/1 COO";"4/1 COOP";"4/1 COOP";"4/1 COOP"}))
=LOOKUP(1,{4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; #VALUE!;#VALUE!;#VALUE!})
=4
=4
Remark : the formula used ROW($1:$10) for easy showing
Regards,
Bosco

20080831, 00:12 #11
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: extracting characters from a cell (XP 2003)
<img src=/S/cool.gif border=0 alt=cool width=15 height=15> Thanks Bosco, that is a great explanation
Jerry