Suchen/Ersetzen in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1359501%22%20slang%3D%22de-DE%22%3EFind%2FReplace%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1359501%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%20everyone%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20cell%20there%20is%20in%20it%2022%2C22%20(18%2C23).%20I%20have%20to%20edit%20it%20in%20a%20way%20that%20only%20stands%20at%2018.23.%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU%20and%20greetings%20from%20Munich%3C%2FP%3E%3CP%3ESusanne%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1359501%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1360194%22%20slang%3D%22en-US%22%3ERe%3A%20Suchen%2FErsetzen%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1360194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F651551%22%20target%3D%22_blank%22%3E%40Susanne_Moszner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuppose%20that%20the%20text%20%26nbsp%3B%3CSPAN%3E22%2C22%20(18%2C23)%26nbsp%3B%3C%2FSPAN%3Eis%20in%20A1%2C%20then%20the%20following%20formula%20will%20extract%2018%2C23%20as%20a%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMID(A1%2CFIND(%22(%22%2CA1)%2B1%2CFIND(%22)%22%2CA1)-FIND(%22(%22%2CA1)-1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20case%20you%20want%20to%20extract%20the%20%3CU%3Evalue%3C%2FU%3E%20of%20the%20numbers%20that%20are%20enclosed%20in%20the%20brackets%2C%20%3CFONT%20color%3D%22%23FF0000%22%3Eand%3C%2FFONT%3E%20provided%20that%20you%20have%20the%20comma%20as%20the%20decimal%20separator%2C%20try%20this%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVALUE(MID(A1%2CFIND(%22(%22%2CA1)%2B1%2CFIND(%22)%22%2CA1)-FIND(%22(%22%2CA1)-1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20I%20suspect%20you%20have%20German%20Excel%2C%20so%20the%20formulae%20would%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEIL(A1%3BFINDEN(%22(%22%3BA1)%2B1%3BFINDEN(%22)%22%3BA1)-FINDEN(%22(%22%3BA1)-1)%0A%0Aand%20%0A%0A%3DWERT(TEIL(A1%3BFINDEN(%22(%22%3BA1)%2B1%3BFINDEN(%22)%22%3BA1)-FINDEN(%22(%22%3BA1)-1))%0A%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hallo Zusammen,

 

ich habe eine Zelle da steht drin 22,22 (18,23). Die muss ich so bearbeiten, das da nur noch 18,23 steht.

Wie kann ich das machen?

 

DANKE und viele Grüße aus München

Susanne

1 Reply
Highlighted

@Susanne_Moszner 

Suppose that the text  22,22 (18,23) is in A1, then the following formula will extract 18,23 as a text.

 

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

 

In case you want to extract the value of the numbers that are enclosed in the brackets, and provided that you have the comma as the decimal separator, try this one:

 

=VALUE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))

 

 

EDIT: I suspect you have German Excel, so the formulae would be:

=TEIL(A1;FINDEN("(";A1)+1;FINDEN(")";A1)-FINDEN("(";A1)-1)

and 

=WERT(TEIL(A1;FINDEN("(";A1)+1;FINDEN(")";A1)-FINDEN("(";A1)-1))