Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: extracting characters from a cell (XP 2003)

    thank you

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: extracting characters from a cell (XP 2003)

    thank you

  6. #6
    2 Star Lounger
    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

  7. #7
    Platinum Lounger
    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  10. #10
    2 Star Lounger
    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

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

Posting Permissions

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