Warm tip: This article is reproduced from serverfault.com, please click
decimal excel vba separator

How do I change decimal dot into decimal comma?

发布于 2020-11-28 14:04:13

I have a column containing numbers with mixed decimal separators.
example

I need to use "," as separator.

How do I change dot into comma?

With ThisWorkbook.Worksheets("RAW").Range("A1")
    .Value = Replace(.Value, ".", ",")
End With

The point is stored as text
look at it

Is there any way to store it as general/numeric?

I tried this

With ThisWorkbook.Worksheets("RAW").Range("A1")
    .NumberFormat = "General"
    .Value = Replace(.Value, ".", ",")
    .NumberFormat = "General"
    .Value = .Value
End With
Questioner
k.Sz
Viewed
0
分享
JvdV 2020-11-28 23:35:54

A single cell could be solved quick and dirty through:

.Value = Replace(.Value, ".", ",")*1

Or:

.Value = CSng(Replace(.Value, ".", ","))

If you happen to have a range to process, you could use:

Sub Test()

With ThisWorkbook.Worksheets("RAW").Range("A1:A3")
    .Replace ".", ","
    .TextToColumns
End With

End Sub

Per column:

Sub Test()

With ThisWorkbook.Worksheets("RAW").Range("C2:D4")
    .Replace ".", ","
    For Each col In .Columns
        col.TextToColumns
    Next
End With

End Sub
k.Sz 2020-11-28 15:17:24

ok but I get error: "Microsoft Office Excel can convert only one column at a time. The range can be many rows tall but no more than one column wide. Try again by selecting cells in one column only" how to iterate through multiple columns?

热门帖子

1
求推荐硬路由（1000 内）
2
学英语的朋友们可以来看看 https://langplayground.top/
3
华中科大的计算机技术的在职硕士，职场认可度如何？
4
十兆宽带 100 块钱一个月我是活在 80 年代吗？
5
关于高质量 屏幕共享的问题
6
我是真的受不了打小报告的三八了！
7
怎么管理自己的文件体系
8
[开发者自荐] AirBattery: 在 Mac 端获取所有设备的电量并显示在 Dock 或状态栏上
9
南京移动 IPV6 被停
10
WireGuard 跨国组网失败后,一个新工具的诞生

热门github

1
A multi-platform library for OpenGL, OpenGL ES, Vulkan, window and input
2
Dev tool that writes scalable apps from scratch while the developer oversees the implementation
3
shadcn/ui, but for Svelte. ✨
4
The Python Risk Identification Tool for generative AI (PyRIT) is an open access automation framework to empower security professionals and machine learning engineers to proactively find risks in their generative AI systems.
5
Performance-portable, length-agnostic SIMD with runtime dispatch
6
ZK Credo
7
OpenCodeInterpreter: Integrating Code Generation with Execution and Refinement
8
Joplin - the secure note taking and to-do app with synchronisation capabilities for Windows, macOS, Linux, Android and iOS.
9
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.
10
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
11
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
12
🎓 Path to a free self-taught education in Computer Science!
13
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
14
A collective list of free APIs
15
📚 Freely available programming books