1. ## 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
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. ## 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
MID = TEIL
SEARCH = SUCHEN
ISERROR = ISTFEHLER

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