Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Seperate Strings (2003)

    hello,

    i have a problem with strings, containing numbers and text. i have to seperate certain informations out of this
    strings. below stand all possible formats of data and the output, i need. i used some text formulas, to get these
    informations, but with 5 numbers of the cr-10xxx my formula does not work.
    the maximum is 4 numbers-> see formula
    =WENN(LINKS(WECHSELN(WECHSELN(A3;"CR-0";"");"BR-0";"");4)="BR-1";RECHTS(A3;5);LINKS(WECHSELN(WECHSELN(A3;"CR-0";"");"BR-0";"");4))
    is there a more simple solution??
    stefan

    information needed output
    0001 ->0001
    1103-00001 ->1103
    BR-09445 ->9445
    BR-10826 ->10826
    CR-08171-300 ->8171
    CR-10058-100 ->10058

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seperate Strings (2003)

    If you want the result as a text string including leading zeros, you can use

    =IF(LEFT(A3,2)="CR",MID(A3,SEARCH("-",A3)+1,SEARCH("-",A3,SEARCH("-",A3)+1)-SEARCH("-",A3)-1),IF(LEFT(A3,2)="BR",MID(A3,SEARCH("-",A3)+1,100),IF(ISERROR(SEARCH("-",A3)),A3,LEFT(A3,SEARCH("-",A3)-1))))

    and if you want a numeric value without leading zeros:

    =VALUE(IF(LEFT(A3,2)="CR",MID(A3,SEARCH("-",A3)+1,SEARCH("-",A3,SEARCH("-",A3)+1)-SEARCH("-",A3)-1),IF(LEFT(A3,2)="BR",MID(A3,SEARCH("-",A3)+1,100),IF(ISERROR(SEARCH("-",A3)),A3,LEFT(A3,SEARCH("-",A3)-1)))))

    On your German language system, use ; instead of , and translate the functions into German:
    VALUE = WERT
    IF = WENN
    LEFT = LINKS
    MID = TEIL
    SEARCH = SUCHEN
    ISERROR = ISTFEHLER

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperate Strings (2003)

    hans, thanks a lot again,
    i'm using the english version of office and the only thing for me to change were the seperating digits from , to ;
    everything works fine. thanks a lot and have a nice weekend.
    stefan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seperate Strings (2003)

    So why did you post a German language version of the formula? I happen to know German, but most Loungers don't...

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperate Strings (2003)

    I took it from a Worddocument, where I put all my Formulas and Functions of my work in.....
    Just say, it happened by accident, I made a copy from the document in my own language.

Posting Permissions

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