AW: zahlen in text-problem
22.07.2008 18:02:00
Erich
Hi Phil,
hier noch eine 4. Variante, deren Ergebnis sich kaum von der ransis (schöner) RegEx-Lösung unterscheidet:
| A | B | C | D | E | U | V | W |
1 | Text | Variante4 | | RegEx |
2 | 62345678 | 62345678 | | | | 62345678 | | |
3 | sdf62345678 | 62345678 | | | | 62345678 | | |
4 | 62345678sdf | 62345678 | | | | 62345678 | | |
5 | sdf62345678sdf | 62345678 | | | | 62345678 | | |
6 | 6234 5678 sdf | 62345678 | | | | 62345678 | | |
7 | 62 34 56789 | 62345678 | | | | 62345678 | | |
8 | 62 34 5678 8 | 62345678 | | | | 62345678 | | |
9 | 62 34 5678 x | 62345678 | | | | 62345678 | | |
10 | sdf 623 45 67 8 sdf | 62345678 | | | | 62345678 | | |
11 | sdf 65 5 5 55 55 x23 45 67 8 sdfsdf 623 45 66666 sdf sdfs 12345678 66666666 | 65555555 | 62345666 | 67866666 | | 65555555 | 62345666 | 67866666 |
12 | in sachen xy vom 12.06.07 61231234 | 61231234 | | | | 61231234 | #BEZUG! | |
13 | in sachen xy vom 12.06.06 61231234 | 61231234 | | | | 66123123 | #BEZUG! | |
14 | in sachen xy vom 12.06.06 61231234 5 Tage | 61231234 | | | | 66123123 | #BEZUG! | |
15 | nr. 6123 1234 abzgl. 3% skto | 61231234 | | | | 61231234 | #BEZUG! | |
16 | 6123 1234 u. 61235678 meyer | 61231234 | 61235678 | | | 61231234 | 61235678 | |
17 | abc | | | | | | | |
Formeln der Tabelle |
Zelle | Formel | U2 | =INDEX(machs($A2); SPALTE(A1)) |
|
Und hier der Code:
Sub BestNr4()
Dim lngZ As Long, ii As Integer, strT As String, lngC As Long
Dim strP As String
' Range("B2:Z99").ClearContents
For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
strT = Replace(Replace(Cells(lngZ, 1), " ", ""), ".", "x")
strT = Replace(Cells(lngZ, 1), " ", "")
lngC = 1
For ii = 1 To Len(strT) - 7
If Mid(strT, ii, 8) Like "##.##.##" Then
ii = ii + 7
Else
strP = Mid(strT, ii, 8)
If Left(strP, 1) = "6" And IsNumeric(strP) Then
lngC = lngC + 1
Cells(lngZ, lngC) = strP
ii = ii + 7
End If
End If
Next ii
Next lngZ
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort