Zeichenausgabe aus String mit bestimmter Position

[Jim90]

Hallo zusammen,

mal eine kurze Frage: Ich möchte aus einem bestehenden String einzelne Zeichen (evtl. mit direkt anschließendem Zeichen ohne Leerschrittseparierung) auslesen, bei der ich die Position bereits kenne, hier ein konkretes Beispiel:

Bekannter String in einer Zelle: "E F# G A B C D#"

Bekannte Position der Zeichen in einer andere Zelle: 1,7

Gewünschte Ausgabe in neuer Zelle: E, D#

Hat das jemand eine Idee, wie ich das konkret umsetzen könnte?

Auf die Schnelle habe ich mal die Funktionen SUCHEN, FINDEN & TEIL probiert aber bin leider nicht ans gewünschte Ergebnis gekommen.

Vielleicht hat einer von euch ja eine Idee

[shift-del]

Jim,

ein erster Wurf.

	A	B	C	D	E	F	G
1	E F# G A B C D#	1	#WERT!	E$F# G A B C D#	0	2	E
2		7	E F# G A B C$D#	E F# G A B C D#$	13	16	D#

Zelle	Formel
C1	=WECHSELN(A$1&" ";" ";"$";B1-1)
D1	=WECHSELN(A$1&" ";" ";"$";B1)
E1	=WENNFEHLER(SUCHEN("$";C1);0)
F1	=SUCHEN("$";D1)
G1	=TEIL(A$1;E1+1;F1-E1-1)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Gast 123]

Hallo

ja, probier mal:   =TEIL(A4;1;1) & ", " &TEIL(A6;8;2)   =  E, D#
Formelaufbau:    = TEIL(Text; Erstes_Zeichen; Anzahl_Zeichen)

mfg  Gast 123

[shift-del]

Wenn jeder Buchstabe nur ein mal vorkommt.

	A	B	C	D	E	F
1	E F# G A B C D#	E F G A B C D	1	E		E
2			7	D	#	D#

Zelle	Formel
B1	=WECHSELN(Tabelle1!A1;"#";"")
D1	=TEIL(B$1;C1*2-1;1)
E1	=WENN(ISTZAHL(SUCHEN(D1&"#";A$1));"#";"")
F1	=D1&E1

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Kuwer]

Hallo Jim,

eine Variante mit einer UDF:

	A	B	C
2	E F# G A B C D#	1,7	E, D#

Formeln der Tabelle

Zelle Formel C2 =ZweiAusText(A2;B2)

' **************************************************************
'  Modul:  Modul1  Typ = Allgemeines Modul
' **************************************************************

Option Explicit

Function ZweiAusText(rngText As Range, rngPositionen As Range)
 Dim varS As Variant
 Dim varP As Variant
 varS = Split(rngText.Value, " ")
 varP = Split(rngPositionen.Value, ",")
 ZweiAusText = varS(varP(0) - 1) & ", " & varS(varP(1) - 1)
End Function

Code eingefügt mit: Excel Code Jeanie


Gruß Uwe

[BoskoBiati]

Hi,

1. wieso eigentlich "E, D#"? Das D steht an Position 8
2. wo kommt der # her? Wenn ich nur ein Zeichen separiere, dann nur das D

Dann würde nämlich das reichen:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	E F# G A B C D#	1	E, C
2		7

Zelle	Formel
C1	=TEIL(WECHSELN(A1;" ";"");B1;1)&", "&TEIL(WECHSELN(A1;" ";"");B2;1)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[lupo1]

A1: E F #G A B C D#
B1: 2,7,4
Wunschergebnis: F D# A

1) ohne Hilfsspalten:

a) Google Spreadsheet (völlig kostenlos; dort wird ARRAYFORMULA statt {} verwendet):

=ARRAYFORMULA(
TEXTJOIN(" ";;
TRIM(MID(SUBSTITUTE($A1;" ";REPT(" ";99));
TRIM(MID(SUBSTITUTE($B1;",";REPT(" ";99));ROW(INDIRECT("1:"&LEN($B1)-LEN(SUBSTITUTE($B1;",";))+1))
*99-98;99))
*99-98;99)))
)

b) xl2016+ und evtl. xl365 (mit Nr. 5):

{
=TEXTVERKETTEN(" ";;
GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";99));
GLÄTTEN(TEIL(WECHSELN($B1;",";WIEDERHOLEN(" ";99));ZEILE(INDIREKT("1:"&LÄNGE($B1)-LÄNGE(WECHSELN($B1;",";))+1))
*99-98;99))
*99-98;99)))
}

2) mit Hilfsspalten (für Excel Online bzw. Excel 2007+): ebenfalls in der Datei (wo man auch mit der o.g. Formel spielen kann) genannt.

Meine (aus 2012) darin enthaltene universelle Stringaufteilungsformel kann man hier verstehen.

[Jim90]

Moin zusammen und wow! Ich bin echt überrascht, wie viel Hilfe man hier in dem Forum bekommt!

1000 Dank an alle, die sich für mich den Kopf zerbrochen haben!

Ich habe jetzt mal alle Ansätze durchprobiert und finde die von Lupo1 am elegantesten da ohne Hilfsspalten und die UDF Lösung finde ich auch super aber da meine VB Kenntnisse doch ziemlich bescheiden sind, bleib ich bei der Lösung von Lupo1.

Ich wünsche euch allen ein tolles Wochenende!

[lupo1]

Hier noch eine "günstige" Lösung auch für alte Excels (getestet mit 2000 und 2010), mittels {=MEHRFACHOPERATION()}:

Code:

Sub TEXTVERKETTENmitUraltExcel(): Workbooks.Add xlWorksheet
'http://www.clever-excel-forum.de/Thread-Zeichenausgabe-aus-String-mit-bestimmter-Position
[A1].FormulaR1C1 = "E F #G A B C D#": [A2].FormulaR1C1 = "'2,7,4"
[A3:A12].FormulaR1C1 = "=IF(ROW(R[-2]C)>LEN(R2C)-LEN(SUBSTITUTE(R2C,"","",))+1,""""," & _
"TRIM(MID(SUBSTITUTE(R1C,"" "",REPT("" "",99)),MID(SUBSTITUTE(R2C,"",""," & _
"REPT("" "",99)),ROW(R[-2]C)*99-98,99)*99-98,99)))"
[A13].FormulaR1C1 = "=TRIM(R[-10]C&"" ""&R[-9]C&"" ""&R[-8]C&"" ""&R[-7]C&"" ""&" & _
"R[-6]C&"" ""&R[-5]C&"" ""&R[-4]C&"" ""&R[-3]C&"" ""&R[-2]C&"" ""&R[-1]C)"
[D1:F1].FormulaR1C1 = "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RAND()*88888889+11111111," & _
"""0\,0\,0\,0\,0\,0\,0\,0"")&"", "",""9,"",),""0,"",),"", "",)"
r = "SUBSTITUTE(ADDRESS(1,RAND()*256+1,4),1,)": q = r & "&"" ""&"
[C2:C9].FormulaR1C1 = "=" & q & q & q & q & q & q & q & r
[C1:F9] = [C1:F9].Value 'Mehrfachoperation mag aktive Zufallszahlformeln nicht!
[C:F].ColumnWidth = 26: [C1].FormulaR1C1 = "=R[12]C[-2]"
[C1:F9].Table RowInput:=Range("A2"), ColumnInput:=Range("A1")
End Sub

__|_______A_______|B|_____________C____________|___________D___________|__________E_________|__________F__________|CODETab von lupo1
 1|E F #G A B C D#| |F D# A                    |3,2,2,6,2,2            |1,4,4,2,1,1         |2,3,8,6,3,5          |
 2|2,7,4          | |AIS US WJ QP ZW ABW ABL EP|WJ US US ABW US US     |AIS QP QP US AIS AIS|US WJ EP ABW WJ ZW   |
 3|F              | |WK AD BB WR AK FC DS ZJ   |BB AD AD FC AD AD      |WK WR WR AD WK WK   |AD BB ZJ FC BB AK    |
 4|D#             | |GU WF BU VN FU Q NY TD    |BU WF WF Q WF WF       |GU VN VN WF GU GU   |WF BU TD Q BU FU     |
 5|A              | |XG VB DB AIV EF TU IC IR  |DB VB VB TU VB VB      |XG AIV AIV VB XG XG |VB DB IR TU DB EF    |
 6|               | |AF QT ADJ RM JD TT ABM FZ |ADJ QT QT TT QT QT     |AF RM RM QT AF AF   |QT ADJ FZ TT ADJ JD  |
 7|               | |SW VH HU MU EW AKL OV ADR |HU VH VH AKL VH VH     |SW MU MU VH SW SW   |VH HU ADR AKL HU EW  |
 8|               | |HG CM AAL AKO LH DI UJ AFK|AAL CM CM DI CM CM     |HG AKO AKO CM HG HG |CM AAL AFK DI AAL LH |
 9|               | |MY AJG AFZ YQ GI AKC EJ XQ|AFZ AJG AJG AKC AJG AJG|MY YQ YQ AJG MY MY  |AJG AFZ XQ AKC AFZ GI|
10|               | |                          |                       |                    |                     |
11|               | |                          |                       |                    |                     |
12|               | |                          |                       |                    |                     |
13|F D# A         | |                          |                       |                    |                     |
Die Mehrfachoperation findet in C1:F9 statt. Aus den Strings in der Vorspalte C werden nach Anweisung in Zeile 1 die Bestandteile im Feld D2:F9 zusammengesetzt. Zuerst dachte ich, Excel sei damit überfordert, aber dann fand ich heraus, dass man die ZUFALLSZAHL()-Berechnungen nicht als Parameter-Einsteuerung für MEHRFACHOPERATION verwenden darf (fangen nämlich derweil erneut an zu rechnen). Deshalb .Value = .Value!

[snb]

Code:

Function F_snb(c00, c01)
   sn = Split(c00)
   
   For Each it In Split(c01, ",")
      F_snb = F_snb & Left(sn(-1 + it) & Space(2), 2)
   Next
End Function

in A1:     E F #G A B C D#
In A2:    2,7,4
In B1:    =F_snb(A1;A2)