Probleem met punten in bedragen in Excel / CSV

[Captain Pervert]

Goeiemorgen,
Ik heb een CSV bestand met productprijzen. Deze worden bij mij aangeleverd met punten als decimaal scheidingsteken. Dus €60,50 staat in de CSV als: 60.50
Tot nu toe geen probleem, ik doe zoeken en vervangen en maak van de punten komma's om het verder te verwerken en er formules mee te kunnen uitvoeren.

Echter zitten er sinds kort ook bedragen in met drie cijfers achter de komma. Bijvoorbeeld: 105.897
Hierdoor interpreteert Excel dit niet als decimaal scheidingsteken, maar als duizendtal scheidingsteken.
Wat dus getoond moet worden als €105,90 wordt nu dus €105897, een bedrag van ruim een ton

Hoe los ik dit op? Anders importeren in Excel? De rij slim converteren, maar hoe? Ik gebruik Excel 2019.

[Hero of Time]

Dit is een van de vele simpele dingen in Excel, als je maar weet wat de reden hiervan is. Dit is namelijk 100% afhankelijk van je regio-instellingen. Zou je die in je Windows aanpassen naar het formaat van de VS, waar ze dus . voor decimaal en , voor duizendtal gebruiken, hoef je niets te zoeken en vervangen.

Hier zit wel een nadeel bij: dit is je nieuwe standaard en bestanden die jij maakt en met anderen deelt die de NL regio gebruikt voor de tekens kan hetzelfde probleem krijgen als wat jij nu hebt.

Je geeft tevens aan dat het om een csv gaat. Die kan je natuurlijk ook gewoon in Notepad openen en daar via zoek & vervang aanpassen voordat je het bestand in Excel opent. Echter, omdat csv standaard met komma's werkt tussen de velden, kan je hiermee dus mogelijk het scheidingsteken vervangen terwijl dat niet de bedoeling is.
Weet je 100% zeker dat er geen komma op andere plaatsen staat in het bestand, anders dan voor het scheidingsteken van de velden, dan kan je die eerst vervangen voor ; en daarna de punten voor komma's.

[PageFault]

Inderdaad Excel leunt heel erg op de regionale instellingen.

Maar uit je verhaal maak ik op dat je al een slag daarvoor doet. Als je als eerste stap dan alle komma's er tussen uit haalt en dan de punten omzet in komma's, dan ben je er toch ook?

[Teun_2]

Je kan of je regio-instellingen aanpassen, of je kan het bestand importeren via powerquery. Die laatste is sowieso de betere manier om .csv in te laden en het bestand al wat op te kuisen naar wat je wil hebben. Het vereist een beetje leerwerk, maar PowerQuery is echt de moeite waard om een beetje te kennen. Het vereenvoudigdt het werken met data uit verschillende bronnen (en zeker .csv) enorm.

Import or export text (.txt or .csv) files

Set a locale or region for data (Power Query)

[ Voor 44% gewijzigd door Teun_2 op 29-09-2022 14:12 ]

[Orinoko]

Je kunt in Excel eventueel de optie "Use system separators" uitvinken. Die optie vind je onder File > Options > Advanced. Je hoeft dan niet per se de regio instellingen te wijzigen.

[Dunka]

Als je het .csv bestand direct in excel opent en daarna de regels splitst via gegevens -> tekst naar kolommen, dan kan je per kolom via de knop geavanceerd het decimaalteken en scheidingsteken voor duizendtallen ingeven.

[Lustucru]

Dunka schreef op vrijdag 30 september 2022 @ 16:59:
Als je het .csv bestand direct in excel opent en daarna de regels splitst via gegevens -> tekst naar kolommen, dan kan je per kolom via de knop geavanceerd het decimaalteken en scheidingsteken voor duizendtallen ingeven.

[Afbeelding]

Dat is waar voor de oudere versies van Excel. Eerlijk gezegd is die wizard voor niet te complexe importbewerkingen wat mij betreft veel gebruiksvriendelijker dan de huidige methode om alles via powerquery te doen.

Je kunt de oude wizard aanzetten onder opties, gegevens en dan komt hij in het gegevens importmenu terecht. Een verademing als je een bestand importeert warvan decimaaltekens etc. afwijken van je systeeminstellingen.

[Modutsketoch]

Het lijkt crimineel belachelijk, maar als je deze actie uitvoert met OpenOffice = freeware, dan doe je deze import vlekkeloos en zonder problemen. Eens geïmporteerd bewaren en nadien openen in Excel.