Data in kolom aanpassen(2)

[assessor]

Data in een kolom aanpassen...

Ik heb een kolom met data dat bestaat uit een variërend aantal letter-cijfer combinaties, zie hieronder een voorbeeld.
-------------------------------------------------------------------------------
B767-200/300 (GE CF6)_B1_B1K2_AC 1.01
B767-200/300 (GE CF6)_B1_B1K2_AC_ETOPS 1.01
MD11 (GE CF6)_B1_B1K2_KL 1.30
A330 (PW 4000)_B1_B1K1_NW 1.04
-------------------------------------------------------------------------------
Wanneer ik deze data met een macro binnenhaal wil ik de hier in het rood aangegeven cijfers + voorgaande spatie weglaten. Ik heb geprobeerd en gezocht tot ik blauw zag.

Hoe vertel ik in mijn macro dat ik vanaf rechts alles tot en met de eerste spatie wil overslaan? Kan ik überhaupt een spatie als delimiter gebruiken?

Dit is wat ik wil overhouden.
-------------------------------------------------------------------------------
B767-200/300 (GE CF6)_B1_B1K2_AC
B767-200/300 (GE CF6)_B1_B1K2_AC_ETOPS
MD11 (GE CF6)_B1_B1K2_KL
A330 (PW 4000)_B1_B1K1_NW
-------------------------------------------------------------------------------

Ik hoop dat een van jullie mij hiermee kan helpen.

 

[Withaar]

Omdat ik je code niet ken heb ik er maar even een Public Function van gemaakt (voor Excel !)

     Public Function Zonderlaatstewoord(t)
       Dim x As String
       Dim y As String
       x = StrReverse(Trim(t))
       y = Mid(x, InStr(x, " ") + 1, Len(t))
       Zonderlaatstewoord = StrReverse(y)
     End Function

Met StrReverse wordt de volgorde van je string omgedraaid, Trim zorgt er voor dat eventuele spaties aan het eind van je bron string geen problemen veroorzaken.
Daarna wordt met InStr de locatie van de eerste spatie opgezocht.
Deze waarde (+1) wordt gebruikt in de Mid functie om het eerste deel weg te gooien.
N.b. de Len(t) heeft geen functionele waarde, er moet alleen een getalwaarde gebruikt worden in deze functie (Mid) en de lengte van t zal altijd voldoende zijn.
Tot slot word de string weer omgedraaid met StrReverse.

Om de code in z'n huidige vorm te gebruiken druk je op ALT + F11 (In Excel!)
Kies bij Invoegen voor Module.
Plakt de code in het lege blad.
Sluit het VBA venster.
type in een cel =Zonderlaatstewoord(A1)

Ps. alle in één regel kan ook, maar is wat minder overzichtelijk;

Public Function Zonderlaatstewoord(t)
Zonderlaatstewoord = StrReverse(Mid(StrReverse(Trim(t)), InStr(StrReverse(Trim(t)), " ") + 1, Len(t)))
End Function

Maar vermoedlijk in je huidige (Access) code wat makelijker te verwerken.

 

[assessor]

Een perfect, duidelijk antwoord, zelfs ik met mijn minimale kennis van VB kon hier uit komen. Ik heb met beide versies gestoeid, zowel in EXCEL als ACCESS.
In EXCEL werkt het probleemloos en ook in ACCESS als ik het vanuit een QUERY aanroep.
Doe ik dit echter vanuit de MACRO waarmee ik de tabellen importeer dan krijg ik de volgende foutmelding:

Rum-time error '94':
Invalid use of Null

En dat drie keer een voor elke (t) neem ik aan, dit heeft echter geen invloed op het uiteindelijke resultaat, het werkt verder prima maar is wel irritant.

Hartelijk dank zover.

 

[Withaar]

Plaats die macro van jou even.
De waarde t moet je wel gelijk zijn aan de variabele die je gebruikt om de waarden in te lezen.

 

[assessor]

Ik ben er inmiddels uit, dit probleem zit niet in de CODE en niet in de MACRO. Het zit in de DATA die ik importeer.
Dit wordt aangeleverd met kolom-namen, kop en voetteksten, vandaar. Ik heb nu een extra regel toegevoegd aan de MACRO die controleert of de data in kolom F6 voldoet aan de criteria om geïmporteerd te worden, dit werkt.

Nogmaals dank voor je hulp.

 

[assessor]

In een vorige vraag heeft Withaar mij hier al een heel eind op weg geholpen maar...
Ik ben op het volgende probleem gestoten en zit weer helemaal vast.
-------------------------------------------------------------------------------
B767-200/300 (GE CF6)_B1_B1K2_AC
B767-200/300 (GE CF6)_B1_B1K2_AC_ETOPS
MD11 (GE CF6)_B1_B1K2_KL
A330 (PW 4000)_B1_B1K1_NW
-------------------------------------------------------------------------------
Wanneer ik deze data met een macro binnenhaal wil ik de hier in het rood aangegeven gedeelte weglaten.

Wat er steeds weggehaald moet worden zijn dus altijd:
De eerste 5 karakters na de tweede "underscore" van rechts.
Makkelijk toch? Niet voor mij.

Dit is wat ik wil overhouden.
-------------------------------------------------------------------------------
B767-200/300 (GE CF6)_B1_AC
B767-200/300 (GE CF6)_B1_AC_ETOPS
MD11 (GE CF6)_B1_KL
A330 (PW 4000)_B1_NW
-------------------------------------------------------------------------------

Ik hoop dat een van jullie mij hiermee kan helpen.

 

[m@rio]

Het is een vervolg vraag op je eerdere vraag daarom heb ik je beide vragen samengevoegt

 

[Wigi]

Wat er steeds weggehaald moet worden zijn dus altijd:
De eerste 5 karakters na de tweede "underscore" van rechts.

Bij jouw 2de voorbeeldje is dit niet zo, dat zal het wellicht moeilijker maken.

 

[assessor]

Moeilijk genoeg voor mij Wigi, ik heb er ettelijke uren aan gespendeerd.
Ondanks help files, Google en zes hele dikke boeken heb ik het nog niet voor elkaar.

 

[Wigi]

Maar deze situatie kan dus wel degelijk voorkomen? Dat het maw. niet steeds tussen de voorlaatste en laatste _ staat?

 

[Axel Hagg]

Dag Assessor,

Sub x()
Dim a As Byte
Dim s As String

s = "B767-200/300 (GE CF6)_B1_B1K2_AC"
a = InStr(InStr(s, "_") + 1, s, "_")
MsgBox Left(s, a) & Mid(s, InStr(a + 1, s, "_") + 1)
End Sub

gegroet,

Axel.

 

[assessor]

Wigi,

Ik heb ook al moeite met rechts van links te onderscheiden zie ik.
Hierboven moet dus staan:
"De eerste 5 karakters na de tweede "underscore" van links."
Nu begrijp ik je vraag.

Axel,

In mijn omzwervingen door allerlei boeken was ik bij de uitleg van het gebruik van InStr ook zo'n voorbeeld tegengekomen maar....
Ik heb een paar 100 verschillende combinaties die moet ik toch niet allemaal afzonderlijk als byte declareren?

vast dank

 

[Wigi]

Zie bijlage.

Zo moeilijk is het niet hoor.

Wigi

 

[assessor]

Wigi,

Dit is inderdaad de bedoeling ik weet alleen niet of ik het in deze vorm kan gebruiken.

Ik kan het beste denk ik, uit de voeten met een stukje VB code zoals Withaar dit voorstelde in het eerste gedeelte van deze vraag.
Dit heb ik in Access in een query opgenomen en werkt perfect voor het weghalen van het laatste gedeelte.
Zo'n zelfde truuc zoek ik nu hiervoor.
Ik ga in ieder geval vast aan de gang met jou oplossing.
Is het teveel gevraagd als ik naar een kleine uitleg van de formule vraag?

vast dank, assessor

 

[Wigi]

Is het teveel gevraagd als ik naar een kleine uitleg van de formule vraag?

Neen, doe maar

 

[assessor]

In dat geval...

Kun je mij een kleine uitleg van de formule geven?

 

[Wigi]

Uiteraard :thumb:

De formule bestaat uit 2 delen:

deel 1 is het deel t.e.m. de 2de _

DEEL(A3;1;C3)

C3 is de plaats in de cel van die 2de _

De VIND.SPEC functie zoekt naar de plaats van de _ in de tekst.

Vervolgens slaan we karakters over. We plakken het deel tekst dat begint op plaats C3, gaan 1 karakter opzij, en dan slaan we er 5 over. De lengte van het laatste deel moet groot genoeg zijn, daarom 300. Gewoon een groot getal nemen.

Snappie?

Wigi

 

[Withaar]

En weer als Public Function
VMD staat voor verwijder midden deel bij gebrek aan een betere naam

Public Function VMD(t) As String
VMD = Mid(t, 1, InStr(InStr(t, "_") + 1, t, "_")) & Mid(t, Len(Mid(t, 1, InStr(InStr(t, "_") + 1, t, "_"))) + 6, Len(t))
End Function

Uitleg:
1e deel
Mid(t, 1, InStr(InStr(t, "_") + 1, t, "_"))
Zoek in t een "_" gebruik de gevonden 'locatie waarde', zoek opnieuw in t naar een "_' maar nu vanaf de gevonden locatie waarde + 1, dus naar de tweede "_" in je string.
Gebruik nu deze 2e locatie waarde om het einde van de string 't' te bepalen.
Met Mid selecteer je uit t vanaf positie 1 t/m 2e gevonden positie "_" een stuk tekst.

Geeft dus B767-200/300 (GE CF6)_B1_

& = plak daar aan vast.

2e deel
Mid(t, Len(Mid(t, 1, InStr(InStr(t, "_") + 1, t, "_"))) + 6, Len(t))
Zoek weer die tweede _ en tel daar 6 bij op.
Gebruik deze waarde als begin positie om de rest uit t te halen, de laatste len(t) is weer nodig omdat er een waarde opgegeven moet worden als je mid gebruikt, en de lengte van t zal altijd een bruikbare zijn.

Geeft AC

Alles bij ekaar B767-200/300 (GE CF6)_B1_AC

* met 'locatie waarde' bedoel ik de x-ste positie in de string

 

[assessor]

Wigi,

Ik snap 'm! begrijp alleen niet waarom je in het tweede gedeelte +1+5 gebruikt en niet gewoon +6 ??? Ik heb het inmiddels in mijn Excel "deel" geprobeerd en dit gedeelte werkt maar...

Withaar,

Jouw code in mijn Access gedeelte testen gaat mij nu niet meer lukken (moet ook een paar uurtjes slapen), ik twijfel er niet aan dat het net zo goed werkt als het vorige deel, maar...

Ik was de "A" vergeten!!!
Hier is geen 1 en 2 van, in dit geval moet ik 4 i.p.v. 5 karakters verwijderen, bijvoorbeeld:
B747-400 (GE CF6)_A_AK1_KL

Hoe pak ik dit nu weer aan?
Ik hoop dat jullie het nog steeds makkelijk vinden.

 

[Withaar]

"4 i.p.v. 5 karakters verwijderen"

Verander de 6 in een 5.

Public Function VMD(t) As String
VMD = Mid(t, 1, InStr(InStr(t, "_") + 1, t, "_")) & Mid(t, Len(Mid(t, 1, InStr(InStr(t, "_") + 1, t, "_"))) + [B]5[/B], Len(t))
End Function

Maar als de lengte (aantal tekens) tussen de tweede _ en de derde _ niet een vaste waarde is, is een aanpassing aan de formule misschien handiger.

Public Function VMD(t) As String
VMD = Mid(t, 1, InStr(InStr(t, "_") + 1, t, "_")) & Mid(t, Len(Mid(t, 1, InStr(InStr(InStr(t, "_") + 1, t, "_") + 1, t, "_"))) - 1, Len(t))
End Function

Verwijderd alles tussen de tweede _ en derde _