Solved: substitute "." with "" and give format

[danovkos]

hi all,
i wrote this problem to other forum, but there nobody help me.
http://www.excelforum.com/excel-programming/673933-substitute-with-and-give-format.html

Pls. can you help me here?
---------------
I want change numbers in format with dots to format without dots. Now it doesnt works good.
How i use it:
I copy a lot of numbers in "bad format (with dots) in excel, then select all numbers and run macro.
f.e.:
10.205.356,46
to
10 205 356,46

this code doesnt works good. If is in the number only comma without dot, it multiply the number by 100 http://www.excelforum.com/images/smilies/frown.gif

What is wrong in this code please?


Sub dot()
'

Const erate = 1
Dim cell As Range
Application.ScreenUpdating = False

For Each cell In Selection
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Next cell
For Each cell In Selection
If IsNumeric(cell.Value) And LenB(cell.Value) Then cell.Value = cell.Value * erate
Application.ScreenUpdating = True

Next cell
Selection.NumberFormat = "#,##0.00"

End Sub

in atach is example the RED number convert wrong

[JONvdHeyden]

Try:

Selection.TextToColumns DataType:=xlDelimited, DecimalSeparator:=",", ThousandsSeparator:="."

Or manually:

Highlight the column with the numbers.

On the menu bar go Data > Text to Columns
Delimited > Next > Next
Hit Advanced
Choose Decimal separator as ,
Choose Thousand separator as .
Hit Ok > Finish

Format range to desired number format.

[Bob Phillips]

Sub dot()
'

Const erate = 1
Dim cell As Range
Application.ScreenUpdating = False

For Each cell In Selection

cell.Replace What:=".", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

If IsNumeric(cell.Value) And _
LenB(cell.Value) Then cell.Value = cell.Value * erate
Next cell

Selection.NumberFormat = "#,##0.00"
Application.ScreenUpdating = True

End Sub

[danovkos]

thx, but it doesnt works...
it do the same as my code

look at atach.
if is there only "," it multiply number and this is wrong :(

[Bob Phillips]

It is very difficult for us to do anything with this as our regional settings are not as yours, the decimal separator is a dot for us.

[JONvdHeyden]

Try:

Sub FormatNum()
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In ActiveSheet.UsedRange
If Left(Right(rCell, 3), 1) = "," Then
MsgBox rCell.Address
With rCell
.Replace what:=".", replacement:="", lookat:=xlPart, MatchCase:=False
.Replace what:=",", replacement:=".", lookat:=xlPart, MatchCase:=False
.NumberFormat = "#,##0.00"
End With
End If
Next rCell
End Sub

[danovkos]

this your code ask me at each cell in sheet, but it multiply it again :(
and it works for all sheet and i want only the selection

[GTO]

Greetings,

As it looks like you just joined recently, Welcome(!). There are some awfully decent folks here, and I'm sure you'll be happy you joined.

Well, as to your issue, I'm flying a bit blind as I'm not familiar with Regional issues as Bob (xld) is, so this might not work at all. So in a throwaway copy of your workbook, you could try selecting the data and running:

In a Standard Module:

Sub dot_2()
Dim cell As Range
Dim strCell As String
'Const erate As Integer = 1

For Each cell In Selection

strCell = cell.Text

If Not InStr(1, strCell, ",", vbTextCompare) = 0 Then

cell.Replace ".", "", xlPart
strCell = cell.Text

strCell = Left(strCell, InStr(1, strCell, ",", vbTextCompare) - 1) _
& "." & _
Right(strCell, (Len(strCell) - InStrRev(strCell, ",", -1, vbTextCompare) + 1) - 1)
cell.Value = CSng(strCell)

End If
Next

Selection.NumberFormat = "#,##0.00"

End Sub

Hopefully nuthin goes Kaboom(!), but seemed worth a shot.

Have a great day,

Mark

[danovkos]

thank you for your try but it gives me error
"Run time error 13"
"Type mismatch"

?

[GTO]

Hi,

Firstly, do a SaveAs, case I goober something here, but:

Delete 'Sub dot()' and/or 'Sub FormatNum' out of the ThisWorkbook Module.

Then insert a Standard Module by: while in VBIDE (the code window), on the menubar, Insert|Module.

Paste the code there.

Reduce VBIDE to half screen so you can see the workbook sheet.

Select some cells that have the values you want to coerce.

Select VBIDE, and click somewhere in the procedure you are trying out.

Press the F8 key repeatedly, and see where (if it still errors) you are in the procedure when it errors.

Mark

[danovkos]

OK
i did it, but still doesnt works.
when i run it for steps (F8) it goes 3x from this line strCell = cell.Text to line next
and then it gives and error
and the first number is again multiplyed and other withou change :(
here is the file

[mdmackillop]

What regional settings are you using?

[danovkos]

slovak
look at atach and in excel i have "Use system separators"

[Bob Phillips]

Isn't it just a case of changing the custom format from

#.##0,00

to

0,00

[danovkos]

no, this didnt help :(
maybe is imposible to do this

[GTO]

OK
i did it, but still doesnt works.
when i run it for steps (F8) it goes 3x from this line strCell = cell.Text to line next
and then it gives and error
and the first number is again multiplyed and other withou change :(
here is the file

Greetings Danovkos,

I couldn't seem to get the same error unless I ran the macro again after already converting. This made sense, as I was using the text of the cell, and glossed straight over the fact that you are using the comma as the decimal seperator.

Maybe:

Sub dot_3()
Dim rCell As Range
Dim strCell As String


For Each rCell In Selection
With rCell
.NumberFormat = "General"
strCell = CStr(rCell.Text)
If Not Len(strCell) < 4 Then
If Not InStr(Len(strCell) - 3, strCell, ",", vbTextCompare) = 0 Then


rCell = Round(CSng(Replace( _
Left(strCell, InStr(Len(strCell) - 3, strCell, ",", _
vbTextCompare) - 1), ".", "", 1) & _
"." & _
Right(strCell, (Len(strCell) - _
InStrRev(strCell, ",", -1, vbTextCompare) + 1) - 1)), 2)
End If
End If
'// Change to suite.//
.NumberFormat = "0.00"
End With
Next
End Sub

...excepting change the .NumberFormat at the end to what works for the locale?

Hope this works,

Mark

[danovkos]

NO :( still no works
:banghead:
look i do everything what you are advise to me
here is the atach.
i capture my screen also if you want i will send it

[GTO]

Hi,

Do your local settings use the comma as the decimal seperator? In other words, here, one-point-five-seven would look like "1.57"

Where you are (which is where by the way?), would this look like "1,57" ?

If so, why didn't you change the .NumberFormat to "0,00" ?

We want to help, but leastwise for me, I am having a hard time following. What you attached is the same as last time, albeit with the new code. It appears (as it did last time) that the former code was run on column one; but when I run it on column two/three it returns what its supposed to, and I cannot get it to accidently multiply the results.

Maybe a screen shot of what it looks like after run?

Don't forget to change the number formatting first though!

Mark

[mdmackillop]

Sub dot()
Dim cel As Range
Dim D As String, T As String
Application.ScreenUpdating = False

T = Application.ThousandsSeparator
D = Application.DecimalSeparator

For Each cel In Selection
If InStr(1, cel, D) > 0 Then
cel.Replace What:="'", Replacement:=D, LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
If InStr(1, cel, ".") > 0 And InStr(1, cel, D) > 0 Then
cel.Replace What:=".", Replacement:=T, LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
cel = cel * 1
End If
End If
Next
End Sub

[danovkos]

yes, yes, yes
this last code works great
thank you very much for your time and i know, that you spent a lot of time with this little code.
It is very usefull for me and it saves me a lot of time
...
one more time, thank you all very much

[mdmackillop]

Happy you finally got it sorted.
Regards
MD

[mdmackillop]

Thinking further, the first If seems superfluous. Try it with
For Each cel In Selection
If InStr(1, cel, ".") > 0 And InStr(1, cel, D) > 0 Then
cel.Replace What:=".", Replacement:=T, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
cel = cel * 1
End If
Next