Comma to period

[Freddie]

Dear clever folks!

I'm living in Sweden where we use komma instead of period as seperator
for the decimal part in numbers.
This is no good since all (almost) programes uses period as a
seperator.
I'm doing some number manipulations in Excel and at the end I'm
suppose to export my data as a text file, but Excel exports it with a
komma instead of period, which my next programe cannot interpret.
I know that changing my regional settings within Windows solves this
problem, but I don't have administrative privilgies on the computer so
that is of no good.

I have done a basic macro that looks like this:

Sub ConvertCommaToPeriod()

Worksheets(2).Columns("B").Replace _
What:=",", Replacement:=".", LookAt:=xlPart ', _
'SearchOrder:=xlByColumns ', MatchCase:=True

End Sub

This works well for data that looks like this:

'1234,5678 (notice the apostrophe in the beginning!)

But fails to recognise

1234,5678 (cell formated as text)

What is up with this?

I could of course insert an apostrophe in the beginning of every cell,
but surely there must be a better way?

Best regards
Freddie

 

[Bernard Liengme]

On the Tool |Option International tab there is a box marked Use System
Separators. If you uncheck this you can fill in what you want for decimal
and thousands separators.
I live in Canada so I normally have numbers like 12.34. Using this method I
was able to get 12,34 without changing the Regional Setting. You just need
to do the reverse - use dot and comma.

best wishes