# Thread: extracting characters from a cell (XP 2003)

1. ## 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

2. ## 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

3. ## 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

thank you

thank you

6. ## Re: extracting characters from a cell (XP 2003)

Try this formula….

=-LOOKUP(1,--("-"&LEFT(A1,ROW(\$1:\$1024))))

Regards
Bosco

7. ## 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!

8. ## 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.

9. ## 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>

10. ## 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

11. ## 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•