Remove comma from column in working code.

[abjac]

HI I have this code and it works fine. I need it to be moderated so it remove the comma.

But important is that it keep all digits in the number.
Example 0,73 can be 73 thats good
1,141.37 shall be 114137

10,746.00 shall be 1074600 (important that it keep the 2 digit after, where the normall will be a comma or . for the decimal )

430,556.86 shall be 43055686

Here is my code. It normal change from text to number with comma as decimal.

There is also a sample sheet to play with. With a command button in sheet 1

Please have a look

Sincerely

Abjac

Please Login or Register  to view this content.

[oeldere]

This recorded macro will do that for you.

Please Login or Register  to view this content.

[abjac]

HI Oeldere. And thanks for your answer.
I need the code to start after the header. So if the range can be from example J1 it will be good.
Sorry did not put a header in below sample. I also need it to keep the zeroes after the decimal before. I need this because i need to paste these numbers in another application, where i need also the zeroes. See example below. It cut off the zeroes in some of the numbers.
Column is not changed ony column J. Try to compare or also maybe try this in the sample.

Please have a look thanks

1,000.00 should be 100000
1,011.60 should be 101160

Sincerely

Abjac

Please Login or Register  to view this content.

[AB33]

Abjac,
You could use CTRL+H, type "," and replace with hit the button space and replace all. The is same as recorded macro

[abjac]

Hi AB33 Thanks could that be put into a macro? tried what you said it did not work. Dont know if i do it correctly. I use excel 2003, dont know if that make a change

[patel45]

Please Login or Register  to view this content.

[AB33]

Please Login or Register  to view this content.

[abjac]

Hi AB33 I make it work, but it cut also the zeroes

Please have a look below thanks

Please Login or Register  to view this content.

I use this code only for column H

Please Login or Register  to view this content.

[watersev]

hi abjac, option, please check attachment, press Run button

[xladept]

Hi Abjac,

Is that my code?? Here's a new version:

Please Login or Register  to view this content.

[AB33]

Please Login or Register  to view this content.

[jindon]

This should do it.

Please Login or Register  to view this content.

[watersev]

hi abjac, corrected code to leave all numbers after dot, please check attachment, press Run button

[abjac]

HI Allot of answered. Thanks allot to all of you. I have tested all of them.
Patel45 your code is great works without any problems and also fast. Thanks
Watersev. Your code works and its fast also. So great code also thanks
Jindon just saw your code. I checked it it works like a charm. Thanks allot

AB33 i changed your code a little. It remove the comma, but i need to run it double so it also remove the dot.
It just have one bad thing.It remove dot and comma for all columns. I tried this to record my self and that was my problem.
But if only one column it will work fine. Thanks allot

xladept. Yes its the code you moderated today for me which is great. I am working on some i have to use on Monday. And i thought i could maybe moderate this one.
The one you send is not working. It was taking long time and i have to stop it. But thanks allot for trying and also for earlier today. Really great help i got there.

So a big thanksyou to all of you for you help

I will mark this as solved.

Have a nice evening to all of you

Sincerely

Abjac

[jindon]

Why loop when it doesn't need to?

[xladept]

I wonder what happened - it ran on your sample in about 5 seconds???

Can you send me a sample where it fails???

[abjac]

Hi xladept.
It failed in this line
Call TextToNo1
I removed this line and its working in few seconds. But it has one error, and maybe i did not explain it good enough. It have to remove also the dots.
The result after remove Call TextToNo1 is peerfect but it need to remove the dots also. With the same result after, with all the zeroes after the comma.
You can have a check, i saw you loaded another code for the other thread, so i will check and test this one now also.

Thanks allot for the answer. This forum is so great. many like to help and i have learned allot from you and other here.

So thank you

Sincerely

Abjac

[xladept]

Hi Abjac,

These should do the trick - but don't run more than once for each Range!

Please Login or Register  to view this content.

[abjac]

Hi And thanks I tried it but it come with a runtime error.
this line

For Each S In R: S = S * 100:

But thanks alot for trying. Its solved and depend of your time, the other ís more important for me.

Thanks allot

SIncerely

Abjac