Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
772to776
772to776
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Rechnungsnummer aus Freitextzelle herauskopieren

Forumthread: Rechnungsnummer aus Freitextzelle herauskopieren

Rechnungsnummer aus Freitextzelle herauskopieren
22.06.2006 23:13:33
Matthias
Hallo zusammen,
habe ein ziemlich kniffliges Problem:
In einer Tabelle habe ich ein Freitext-Feld, in dem irgendwo die Rechnungsnummer steht und zwar in folgendem Format: bla,bla,bla Rechnungs# 234567 bla bla.
Nun waere ein Makro prima, das nur die Rechnungsnummer alleine aus dieser Zelle rauskopiert, und in die nebenstehende Zelle kopiert.
Weiteres Problem:
Da das ganze haendisch eingetragen wird, steht manchmal Rechnungs# 234567 und manchmal Rechnungs#234567 in der Zelle.
Das ganze sollte von 1 bis x Zeilen laufen.
Ich waere super dankbar, wenn mir jemand helfen koennte. Die existierenden Beitraege im Forum haben mir leider nicht wirklich weitergeholfen.
Danke im Voraus.
Matthias
Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Rechnungsnummer aus Freitextzelle herauskopier
22.06.2006 23:26:40
Josef
Hallo Matthias!
Dazu brauchst du kein Makro.
Tabelle1
  
 AB
1rechnungs #123456 blabla123456
2rechnungs# 987654bbbla987654
3rechnungs # 123456789 bla bla123456789
Formeln der Tabelle
B1 : {=TEIL(LINKS(A1;MAX(WENN(ISTZAHL(TEIL(A1;SPALTE(1:1);1)*1);SPALTE(1:1))));VERGLEICH(WAHR;ISTZAHL(TEIL(A1;SPALTE(1:1);1)*1);0);LÄNGE(A1))*1}

B2 : {=TEIL(LINKS(A2;MAX(WENN(ISTZAHL(TEIL(A2;SPALTE(2:2);1)*1);SPALTE(2:2))));VERGLEICH(WAHR;ISTZAHL(TEIL(A2;SPALTE(2:2);1)*1);0);LÄNGE(A2))*1}

B3 : {=TEIL(LINKS(A3;MAX(WENN(ISTZAHL(TEIL(A3;SPALTE(3:3);1)*1);SPALTE(3:3))));VERGLEICH(WAHR;ISTZAHL(TEIL(A3;SPALTE(3:3);1)*1);0);LÄNGE(A3))*1}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  


Gruß Sepp


		

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: Rechnungsnummer aus Freitextzelle herauskopier
                                            

                    

                                                    22.06.2006 23:32:10
                                            

                                

                                            Matthias
                                    

            

        

        

                            
Hallo Josef, 

vielen Dank fuer die superschnelle Antwort. Nun habe ich aber ein Problem, 

da in der selben Zelle mehrere Nummern stehen koennen (siehe Beispiel):

bla,bla,...CRA# 38872 SO# 40242717INV# 706324...

Das einzige was immer sicher vor der Rechnungsnummer steht ist INV#, allerdings wie gesagt, mal mit und mal ohne Leerzeichen. 

Vielleicht weisst Du hierfuer ja auch eine Loesung. Waere prima!

Gruss aus USA

Matthias

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: Rechnungsnummer aus Freitextzelle herauskopier
                                            

                    

                                                    22.06.2006 23:47:20
                                            

                                

                                            Josef
                                    

            

        

        

                            
Hallo Matthias!

Dann so.


Tabelle1
  
 AB
1cvg# 123 bcl scf# 456 gdf inv#77654 dvf377654
2cvg# 123 bcl scf# 456 gdf INV#3398756dvf33398756
3cvg# 123 bcl scf# 456 gdf inv#  9876654dvf39876654
4cvg# 123 bcl scf# 456 gdf inv#4432gg dvf34432
Formeln der Tabelle
B1 : {=LINKS(GLÄTTEN(TEIL(A1;SUCHEN("inv#";A1)+4;99));SUMME(1*(ISTZAHL(LINKS(GLÄTTEN(TEIL(A1;SUCHEN("inv#";A1)+4;99));SPALTE($1:$1))*1))))*1}

B2 : {=LINKS(GLÄTTEN(TEIL(A2;SUCHEN("inv#";A2)+4;99));SUMME(1*(ISTZAHL(LINKS(GLÄTTEN(TEIL(A2;SUCHEN("inv#";A2)+4;99));SPALTE($1:$1))*1))))*1}

B3 : {=LINKS(GLÄTTEN(TEIL(A3;SUCHEN("inv#";A3)+4;99));SUMME(1*(ISTZAHL(LINKS(GLÄTTEN(TEIL(A3;SUCHEN("inv#";A3)+4;99));SPALTE($1:$1))*1))))*1}

B4 : {=LINKS(GLÄTTEN(TEIL(A4;SUCHEN("inv#";A4)+4;99));SUMME(1*(ISTZAHL(LINKS(GLÄTTEN(TEIL(A4;SUCHEN("inv#";A4)+4;99));SPALTE($1:$1))*1))))*1}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Oder im englischen Excel

{=LEFT(TRIM(MID(A1,SEARCH("inv#",A1)+4,99)),SUM(1*(ISNUMBER(LEFT(TRIM(MID(A1,SEARCH("inv#",A1)+4,99)),COLUMN($1:$1))*1))))*1}
<div><font color=#008000>Gruß Sepp</font></div>


                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            Rechnungsnummer nach "Inv#"
                                            

                    

                                                    23.06.2006 00:50:33
                                            

                                

                                            FP
                                    

            

        

        

                            
Hallo Sepp,

diesmal nur eine "normale" Lösung:


Tabelle2
  
 AB
12cvg # 1234 bcl scf #567 gdf inv#112201 bla112201
13cvg # 1234 bcl scf #567 gdf inv# 334402bla334402
14cvg # 1234 bcl scf #567 gdf inv# 2bla2
Formeln der Tabelle
B12 : =LINKS(GLÄTTEN(TEIL(A12;SUCHEN("inv#";A12)+4;99));ANZAHL(LINKS(TEIL(A12;SUCHEN("inv#";A12)+4;99);{1.2.3.4.5.6.7.8.9})+0))+0

B13 : =LINKS(GLÄTTEN(TEIL(A13;SUCHEN("inv#";A13)+4;99));ANZAHL(LINKS(TEIL(A13;SUCHEN("inv#";A13)+4;99);{1.2.3.4.5.6.7.8.9})+0))+0

B14 : =LINKS(GLÄTTEN(TEIL(A14;SUCHEN("inv#";A14)+4;99));ANZAHL(LINKS(TEIL(A14;SUCHEN("inv#";A14)+4;99);{1.2.3.4.5.6.7.8.9})+0))+0


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Servus aus dem Salzkammergut

Franz

                    

                                    

                    


    
Anzeige

    

        
                    
            
                
    



                

                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: Rechnungsnummer nach "Inv#"
                                            

                    

                                                    26.06.2006 15:12:42
                                            

                                

                                            Matthias
                                    

            

        

        

                            
Hallo Franz, 

vielen herzlichen Dank fuer Deine Hilfe. Nun habe ich aber festgestellt, dass in manchen Feldern statt Inv# das Wort Invoice ausgeschrieben ist. Wie kann ich beide Varianten, also Inv# oder Invoice# in die Suche einbeziehen. ich habe es mit der Oder () Funktion probiert, klappt aber nicht, ich glaube, weil das Wort Invoice laenger ist als INV. 

Vielleicht kannst Du mir hierbei auch noch helfen. Das waere spitze. 

Vielen Dank vorab und eine gute Woche. 

Matthias

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: Rechnungsnummer nach "Inv#"
                                            

                    

                                                    26.06.2006 20:01:53
                                            

                                

                                            FP
                                    

            

        

        

                            
Hallo Matthias,

na dann halt so:


Tabelle2
  
 AB
12cvg # 1234 bcl scf #567 gdf invoice#112201 bla112201
13cvg # 1234 bcl scf #567 gdf inv# 334402bla334402
14cvg # 1234 bcl scf #567 gdf inv# 2bla2
15cvg # 1234 bcl scf #567 gdf invoice#2bla2
Formeln der Tabelle
B12 : =LINKS(GLÄTTEN(TEIL(A12;MIN(SUCHEN({"inv#"."ice#"};A12&"inv#ice#"))+4;99));ANZAHL(LINKS(TEIL(A12;MIN(SUCHEN({"inv#"."ice#"};A12&"inv#ice#"))+4;99);{1.2.3.4.5.6.7.8.9})+0))+0


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

Servus aus dem Salzkammergut

Franz

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: Rechnungsnummer nach "Inv#"
                                            

                    

                                                    27.06.2006 13:11:55
                                            

                                

                                            Matthias
                                    

            

        

        

                            
Hallo Sepp, 

vielen herzlichen Dank fuer Deine Hilfe! Habe das Problem geloest und mein Chef (ein Ami) hat sich total bei mir bedankt, da er das bisher immer haendisch (ziwschen 6 und 10000 Zeilen) gemacht hat. Doch Dir gebuehrt ein Grossteil des Lobes. Also Danke und ich bin immer wieder erstaunt, wie man sich so gut mit Excel auskennen kann. 

Viele Gruesse aus Amiland.

Matthias

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: Rechnungsnummer aus Text extrahieren
                                            

                    

                                                    23.06.2006 00:37:07
                                            

                                

                                            FP
                                    

            

        

        

                            
Hallo Sepp,

habe auch 2 Lösungen gefunden: 1 x "Arrayformel", 1 x "normal"


Tabelle2
  
 ABC
1blabla Rechnungs-Nr. #112201 blablabla112201112201
2blabla Rechnungs-Nr. # 123402blabla123402123402
3blabla Rechnungs-Nr.# 123403blablablabla123403123403
4bla Rechnungs # 012349 bla bla bla1234912349
5   
6   
7   
8   
9   
10bla bla Rechnungs #12.345 bla1234512345
Formeln der Tabelle
B1 : {=TEIL(A1;MIN(SUCHEN(ZEILE($1:$10)-1;A1&ZEILE($1:$10)-1));ANZAHL(TEIL(A1;ZEILE($1:$40);1)+0))+0}

C1 : =TEIL(A1;MIN(SUCHEN({0.1.2.3.4.5.6.7.8.9};A1&{0.1.2.3.4.5.6.7.8.9}));LÄNGE(A1)*10-SUMME(LÄNGE(WECHSELN(A1;{0.1.2.3.4.5.6.7.8.9};))))+0

B10 : {=TEIL(WECHSELN(A10;".";);MIN(SUCHEN(ZEILE($1:$10)-1;A10&ZEILE($1:$10)-1));ANZAHL(TEIL(A10;ZEILE($1:$40);1)+0))+0}

C10 : =TEIL(WECHSELN(A10;".";);MIN(SUCHEN({0.1.2.3.4.5.6.7.8.9};A10&{0.1.2.3.4.5.6.7.8.9}));LÄNGE(A10)*10-SUMME(LÄNGE(WECHSELN(A10;{0.1.2.3.4.5.6.7.8.9};))))+0
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

In Zeile 10 eine Lösung falls 1000-er Trennzeichen vorkommen

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                
    ;

    
            
 

    
    
                        


    
Anzeige

    

        
                    
            
                
    



                


    
    
    

    
            


    
Anzeige

    

        
                    
            
                
    



        

    

        
        Infobox / Tutorial
    

    

        

            
Rechnungsnummer aus Freitextzelle extrahieren





Schritt-für-Schritt-Anleitung


Um die Rechnungsnummer aus einer Freitextzelle in Excel auszulesen, kannst du folgende Schritte befolgen:


    

  1. Zelle auswählen: Wähle die Zelle aus, in der sich der Freitext befindet (z. B. A1).
    2. 

  2. Formel eingeben:

      

    • Für Rechnungsnummern, die mit "INV#" beginnen, verwende die folgende Matrixformel in der benachbarten Zelle (z. B. B1):

      =LINKS(GLÄTTEN(TEIL(A1;SUCHEN("inv#";A1)+4;99));SUMME(1*(ISTZAHL(LINKS(GLÄTTEN(TEIL(A1;SUCHEN("inv#";A1)+4;99));SPALTE($1:$1))*1))))*1)
      • 

    • Drücke STRG + SHIFT + RETURN, um die Formel als Matrixformel einzugeben.
      • 

    3. 

  3. Formel nach unten ziehen: Ziehe die untere rechte Ecke der Zelle nach unten, um die Formel auf die anderen Zellen anzuwenden.
    4. 





Häufige Fehler und Lösungen


    

  • Fehlermeldung bei der Formel: Stelle sicher, dass du die Formel als Matrixformel eingibst, indem du STRG + SHIFT + RETURN drückst.
    • 

  • Falsche Ergebnisse: Überprüfe, ob die Rechnungsnummern immer mit "INV#" oder "Invoice#" beginnen. Bei unterschiedlichen Formaten, wie "Rechnungs#", musst du die Suchbegriffe in der Formel anpassen.
    • 





Alternative Methoden


Falls du keine Matrixformel verwenden möchtest, kannst du die folgende einfache Formel verwenden:


    

  1. Verwende die SUCHEN-Funktion, um die Position von "inv#" oder "invoice#" zu finden.
    2. 

  2. Nutze TEIL, um die Rechnungsnummer zu extrahieren.
    3. 



Beispiel:


=TEIL(A1;SUCHEN("inv#";A1)+4;LÄNGE(A1)-SUCHEN("inv#";A1)-3)


Diese Methode ist weniger flexibel, wenn die Rechnungsnummer in verschiedenen Formaten vorliegt.




Praktische Beispiele


Hier sind einige Beispiele, die du in deiner Tabelle verwenden kannst:






A (Freitext)
B (Rechnungsnummer)






blabla Rechnungs # 112201 blabla
112201




cvg# 123 bcl scf #567 gdf inv#77654
77654




cvg # 1234 bcl scf #567 gdf invoice# 334402bla
334402






Verwende die oben genannten Formeln, um die Rechnungsnummern in Spalte B zu extrahieren.




Tipps für Profis


    

  • Filtern und Sortieren: Du kannst die Tabelle filtern, um nur die Zellen anzuzeigen, die Rechnungsnummern enthalten.
    • 

  • Fehlersuche: Verwende die ISTFEHLER-Funktion, um sicherzustellen, dass deine Formeln nicht zu Fehlern führen, wenn die Suchbegriffe nicht gefunden werden.
    • 

  • VBA-Makros: Für komplexere Anforderungen, wie das Verarbeiten von großen Datenmengen, kannst du VBA-Makros verwenden, um die Rechnungsnummern effizient zu extrahieren.
    • 





FAQ: Häufige Fragen


1. Wo steht die Rechnungsnummer in den Freitextzellen?

Die Rechnungsnummer kann in verschiedenen Formaten stehen, z. B. "INV# 123456" oder "Invoice#123456". Verwende die entsprechenden Formeln, um sie herauszulesen.


2. Was ist der Unterschied zwischen "inv#" und "Invoice#"?

Beide Begriffe können verwendet werden, um Rechnungsnummern anzuzeigen. Stelle sicher, dass deine Formeln beide Varianten berücksichtigen, um alle Rechnungsnummern korrekt zu erfassen.

        

    



        
    
    
            


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

    
    
    

    
    

    

        

            

                
                    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
                
            

            

                

                
            

        

    




    
    

    
    
            
 

            
            

                
                
                
                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige

    

        
                    
            
                
    



                

                
                
                
                


    
Anzeige