Duizendtallen gescheiden door komma's

[ner0987]

Hallo @ll,
Mogelijk is er een topic van, via de zoekfunctie kon ik niets vinden.
Mijn excuses wanneer ik verkeerde zoekwaarden gebruikt zou hebben.

Ik ben momenteel bezig met een excelletje waar ik via externe gegevens vanop het web een tabel laat importeren.
De waarden in de tabel zijn voor de duizendtallen gescheiden door een komma in plaats van een punt.
Excel ziet dit dus automatisch als decimale getallen en maakt van 23,530 bvb 23,53.
Er zijn ook getallen boven het mio, die ziet hij als tekst door de 2 komma's, waardoor ik dus geen berekeningen met deze cijfers kan uitvoeren.
Dit tweede zou mogelijk opgelost kunnen worden door een macro die de komma's wijzigt in punten, maar het probleem voor de getallen van minder dan 1mio blijft daardoor bestaan.
Is er een manier waarop ik dit kan vermijden?

Alvast bedankt!

 

[oeldere]

Allereerst is dan de vraag hoe de gegevens vanuit het externe betand ingelezen worden in Excel.

2e vraag zou zijn om een voorbeeldbestandje ontdaan van gevoelige info; zodat je kunt zien hoe je data eruitziet.

 

[ner0987]

Normaal voeg ik altijd wel een voorbeeldbestand toe, alleen ligt dat hier dus moeilijk gezien de link met mijn persoonlijke tabel.
Daarlangs leek het me niet nodig voor dit probleem.

De tabel wordt geïmporteerd via tabblad gegevens -> externe gegevens van web.
Dan krijg ik in excel simpelweg een tabel met allemaal waarden waarvan de duizendtallen gescheiden zijn door komma's.

Ik kijk zo meteen even of ik een excelletje kan linken aan een willekeurig tabelletje vanop het net om een voorbeeld te creëren.
Het toegevoegde voorbeeldbestand is gelinkt aan een tabel op een eigen testpagina:

Bekijk bijlage voorbeeldtabel.xls

 

[oeldere]

Bestaan er cijfers achter de komma; of komen die in je tabel niet voor?

 

[ner0987]

Oei, had het nog in mn hoofd dit te vermelden, maar ben het vergeten.
Er komen inderdaad geen decimale getallen voor in mijn tabel.

 

[roeljongman]

dit probleem is echt heel simpel op te lossen.. bijna niet de moeite om er een macro van te maken...


1. selecteer het gebied waar de komma's in staan
2. druk CTRL+H (zoeken en vervangen0
3. zet een komma in zoeken en laat vervangen leeg
4. kies alles vervangen

et voila.. uw getallen zijn weer netjes..

maar ja omdat een macrootje toch weer vier handelingen scheelt als je het vaak moet doen.. de volgende code.. opgenomen met de recorder en een beetje aangepast.

Sub VerwijderKommas()
    Range("B1").currentregion.select
    Selection.Replace What:=",", Replacement:="", LookAt:=xlPart
End Sub

en zouden er wel punten in staan met getallen daarachter dan verwijder je eerst alle komma's en daarna vervang je de punten door komma's
(nie andersom doen dan word het een puinhoop )

Edit: toch maar currentregion gebruikt in de macro.. is veiliger als er andere data ranges zijn op hetzelfde tabblad

 

[ner0987]

Bedankt voor de moeite, maar als het echt zo simpel op te lossen was had ik het hier niet hoeven te plaatsen. ^^

Wanneer ik de tabel inlaad zal hij van 23,530 automatisch 23,53 maken.
Wanneer ik dan de komma's verwijder of verander in punten heb ik 23.53 in plaats van 23.530

 

[roeljongman]

ja dan moet je gewoon zorgen dat de getallen niet op 0 eindigen..


nee, grapje ik had even gemist dat de getallen kleiner als 1 miljoen wel als getal worden herkend. toch kan dit trucje wel werken als je voor mekaar krijgt dat de webquery allen als tekst binnenhaalt.. door de zoek en vervang actie worden er dan ook direct alles omgezet naar waarden..

maar ik zal ook nog naar een alternatieve macro kijken.. de oplossing voor de getallen kleiner dan 1 mio is volgens mij vermenigvuldigen met 1000 want als het getal 23,537 is word dat 23537 en als het getal 23,53 is word dat 23530 en dat is wat je wilt
voor de miljoenen blijft het toch een kwestie van komma's verwijderen.

 

[ner0987]

Van mij mag die idd volledig als tekst geimporteerd worden. Van daaruit kan ik zelf weer verder om de data naar getallen om te zetten.
Ik ken bitter weinig van macro's, en via de opnamefunctie heb ik deze verkregen.
Wat moet ik hieraan toevoegen/aanpassen om het als tekst te importeren?

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;link naar data" _
        , Destination:=Range("$B$2"))
        .Name = "tekst"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False

 

[oeldere]

Misschien dat andere hier wel verder mee komen.

Ik heb het wel zover gekregen, dat je met tekst 3 cijfers achter de komma krijgt.

Als je dan CNTL + H doet , wordt . dan krijg je toch weer 23,15

=ALS(LINKS(RECHTS(B1;3);1)=",";TEKST(B1;"0,000");ALS(LINKS(RECHTS(B1;2);1)=",";TEKST(B1;"0,000");B1))

 

[roeljongman]

ja dan moet je gewoon zorgen dat de getallen niet op 0 eindigen..


nee, grapje ik had even gemist dat de getallen kleiner als 1 miljoen wel als getal worden herkend. toch kan dit trucje wel werken als je voor mekaar krijgt dat de webquery allen als tekst binnenhaalt.. door de zoek en vervang actie worden er dan ook direct alles omgezet naar waarden..

maar ik zal ook nog naar een alternatieve macro kijken.. de oplossing voor de getallen kleiner dan 1 mio is volgens mij vermenigvuldigen met 1000 want als het getal 23,537 is word dat 23537 en als het getal 23,53 is word dat 23530 en dat is wat je wilt
voor de miljoenen blijft het toch een kwestie van komma's verwijderen.

 

[oeldere]

De tip van roel heeft geleid tot deze oplossing.

=ALS(LENGTE(B2)=9;VERVANGEN(B2;2;1;".")*1000;ALS(LENGTE(B2)=10;VERVANGEN(B2;3;1;".")*1000;+B2*1000))

Misschien dat de macro-kenners dit in een macor kunnen pakken, zodat je geen hulpkollommen nodig hebt.

 

[ner0987]

Hallo iedereen, sorry voor mijn late reactie, maar ik heb een drukke werkweek achter de rug...

De oplossing hierboven leek me prachtig en perfect, alleen ben ik net tegen een probleem gestoten.
Wanneer de data in de cel minder dan 1000 is word dit vermenigvuldigd met 1000, wat dus zal resulteren in een foutieve waarde.
Ik dacht dit op te lossen door een ALS functie toe te voegen die geen wijzigingen toebrengt wanneer de lengte in de cel minder dan 4 tekens bedraagt.
Alleen stoot ik dan op het probleem dat wanneer er een waarde als 25,000 ingeladen word, deze dan zal weergegeven worden als 25.
De kans dat er een zodanig ronde waarde wordt ingeladen is zéér klein, maar de mogelijkheid bestaat.

Zie ik de juiste logica niet om dit probleem op te lossen, of zal ik hiermee moeten leven?

In ieder geval al erg bedankt voor jullie bijdrage!
Moest het probleem niet kunnen opgelost worden ben ik hiermee toch al een heel stuk verder!

edit: @oeldere: ik heb geen probleem met het gebruik van hulpkolommen. De sheet zal maximaal 100x10 regels/kolommen groot zijn, en de uiteindelijke print gebeurt vanuit een ander tabblad.

 

[Naarling]

Ik heb hier geen excel maar ik zie twee, volgens mij vrij simpele mogelijkheden:
1) Zet de gegevens eerst in een ander programma, zoals notepad, en verwissel de punten en comma's voordat je de beol in excel zet.
2) Stel excel, tijdelijk, in om punten als decimalen en comma's als duizendtal-scheider te zien.

 

[ner0987]

Hoi naarling, bedankt voor je bijdrage

Alleen enkele probleempjes...
Het excelbestand is via externe gegevensverbinding gelinkt aan een tabel op het net. Jouw methode 1 is momenteel mijn huidige manier van werken, maar omdat ik dagelijks een update dien te maken wilde ik het dus gaan automatiseren.
Ik gebruik excel in het dagelijkse leven -zowel thuis als op het werk- zo goed als continu, elke dag opnieuw voor dit éne bestand die landinstellingen veranderen zie ik eerlijk gezegd ook niet zitten...

Toch bedankt!

 

[oeldere]

Probeer mijn oplossing nog eens.

Ik heb rij 7 (F7) toegevoegd, met als waarde 25,000.

In cel R7 kom de waarde 25.000.

Dit lijkt mij de goede uitkomst.

Deze uitkomst stond in mijn vorige bestand ook in rij R6 (22.000).

1) Dus waarschijnljk mis ik iets in je constatering.

Of

2) Heb jij mijn bestand niet goed bekeken.

Graag even reactie retour.

Heb je hier vragen of opmerkingen over, laat het gewoon even weten.

 

[ner0987]

Hoi oeldere,

ik was niet duidelijk genoeg of jij snapte mijn probleem niet gok ik.

Wanneer we even de bron erbij nemen van waaruit het voorbeeld zijn data haalt zie je dat er waarden van minder dan 1000 worden ingeladen. Deze resulteren in een einduitkomst in het 1000-voud van het werkelijke getal.

Uitgaand vanuit dit probleem stelde ik voor om cellen met een inhoud van minder dan 4 tekens niet te wijzigen, maar dat is ook geen oplossing aangezien dán het probleem van ronde duizendtallen pas opduikt...

 

[roeljongman]

@oeldere wat jij mist is dat als er een "echte" 25 of 250 word opgehaald dat die ook word omgezet in 25.000 of 250.000 want die zat niet in jou testgebied. excel ziet het verschil dan niet meer

kortom 25,000 gaat via 25 naar 25000 en dat is goed, maar 25 gaat via 25 naar 25000 en das niet goe.. test dat maar eens uit.

@ner0987 misschien dat onderstaande werkt als refresh macro, ik weet niet hoe je je webquery nu refreshed? maar ik denk dat als je deze macro aan een knop koppelt en via die knop refreshed dat het zo kunnen (moeten) werken.

Sub vbHelpmij()
'schakelt standaard indeling uit en vervangt door Engelse instelling
' (dat geld voor alle documenten op dat moment en is ook zelf in te stellen via opties > geavanceerd) 
    With Application
        .DecimalSeparator = ","
        .ThousandsSeparator = "."
        .UseSystemSeparators = False
    End With

'start het vernieuwen van alle vernieuwbare objecten in je workbook.
    ActiveWorkbook.RefreshAll  

'herstelt de systeeminstelling voor kommascheiding
    Application.UseSystemSeparators = True
End Sub

Edit: O ja, omdat je een instelling wijzigt die voor alle workbooks geld, test het de eerste keren ui met alleen dat workbook open en zorg dat er geen onopgeslagen data is die je niet kwijt wilt..
Als de macro werkt binnen die sheet kijk dan nog even naar de (tijdelijke) invloed op andere sheets die openstaan.

Volgens mij moet het wel goed gaan maar test het even goed en bedenk een paar situaties waar punten en komma's van belang zijn en wat er gebeurd als je de punt en de komma tijdelijk verwisseld van functie..

 

[oeldere]

Ok, ik snap wat jullie bedoelen.

Beide, bedankt voor de reactie.

In dat geval zou ik verwachten dat een "echte" 25 wordt ingelezen als 0,025.

In dat geval zou mijn formule wel werken.

Maar een "echte" 25 wordt dus ook als 25,000 ingelezen?

 

[ner0987]

Beiden bedankt voor jullie reactie.

@roel: bij het nalezen van je macro leek die te kunnen werken, hoewel het, om een voor mij onbekende reden, helemaal geen invloed heeft op mijn bestand, noch voorbeeldbestand, noch andere bestanden.
Volgens mij moest decimalSeparator een punt, en thousandsSeparator een komma worden in het eerste stukje van de macro, maar ook hier geen resultaat.

@oeldere: een echte 25 word ingelezen als een gewone 25. In de hulpkolommen word deze ook nog weergegeven als 25, maar in het resultaat krijgen we 25.000.

De oplossing met formules lijkt me nu onmogelijk aangezien er geen onderscheid gemaakt kan worden tussen een echte 25, en een 25 afgeleid vanuit 25,000