Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

VBA Excel 2000 - Replace

Dear Experts,

Could you please have look the attached file, basically its A column is a download from an outer system.

You can see for example in cells A4 or A20, that the download saved the quantities in such format
       1.774,000
       5.100,000

My target would be to replace the "." characters so getting
1774
5100

Basically I have a macro which I assume should do it, but this creates the cell values like 1774000 and 5100000 on the example

Sub SimpleReplaceInColumn()
[A:A].Replace ".", vbNullString
End Sub

Could you please advise how this code should be modified to bring the quantities according to the target? It is interesting because selecting menu Edit/Replace and Find what "." to replace with nothing, that works

thanks,
Download.xls
0
csehz
Asked:
csehz
  • 4
  • 2
1 Solution
 
csehzIT consultantAuthor Commented:
Maybe a small addition the in Regional settings if that has some effect, the Decimal symbol is ",", but on that I can not change as has fear that in other files Access import specifications would be confused.

Also attached now a picture about the problem

thanks,
Download.jpg
0
 
KnutsonBMCommented:
does everything end with ,000?

-Brandon
0
 
csehzIT consultantAuthor Commented:
Brandon no, the download save logic seems that if the quantity is less than 1000, those are as numbers without "."

But if the quantity is greater or equal than 1000, for those the pattern is 1.000,000. So using "." for thousands, "," for decimals.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
csehzIT consultantAuthor Commented:
I assume your question is whether for sure all quantity has always ,000 as decimal, in this example yes but can not guarantee that in all download such would be
0
 
Jignesh TharSenior ManagerCommented:
Csehz - Try below code. It saves your decimal point before doing away with "." and replaced decimal back.

It will interprete 1.774,123 as 1774.123 apart from what you described.


Sub SimpleReplaceInColumn()
[A:A].Replace ",", vbCrLf ' Save decimal position
[A:A].Replace ".", vbNullString
[A:A].Replace vbCrLf, "." ' Restore decimal position
End Sub

Open in new window

0
 
csehzIT consultantAuthor Commented:
That works perfectly :-) You are amazing thanks very much your help.

On my machine even the 1.774,123 example brought 1774,123 which is correct
0
 
Jignesh TharSenior ManagerCommented:
csehz - Glad it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now