replace a Dot with Comma Because of a Formula
Hello,
I got a Sheet(export FreshBooks) with testing information and cost.
On cell ( I ) i get cost send tru from my co-worker with dot between it example: 125.00/25.00
because of this my formula doesn't work ony my Sheet(Output) i know that i need to replace this with comma (,) (this is because i work with (,) in the Netherlands and the (.) is for GB usage)
is there a VBA line for this? i know there is option for find dot and replace with comma (,).
greets,
Test.xlsm
I'll try to explain that situation better.
I'm using commas as decimal separator, but data that I work with, comes from another application (web-based) that uses dots as separator. When I paste data tables to a Excel chart, all numbers are handeled as text. My objective is to convert them to the numbers.
You can substitute it:
Formula:
Please Login or Register to view this content.
Hint: You must first change , (thousand delimiter) with nothing, and then . with ,
Hello
Perhaps something like this?
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
The code Works.
But ..if i have big numbers like 1.000.000 i will chance that i will get a #value
Last edited by Shimazu; 03-10-2014 at 11:19 AM.
............................
the code works fine but when i put new information in it with bigger numbers like 8,264,46 it will say #valeu on sheet OUTPUT.
Version 2.xlsm
Replacing commas/dot as decimal separator
Hello,
I'm using commas as decimal separator, but data that I work with, comes from another application (web-based) that uses dots as separator.
When I paste data tables to a Excel chart, all numbers are handeled as text. My objective is to convert them to the numbers.
I already have this (this works) but for bigger numbers like 7,000,000 i will get a #VALUE!
Is there a fix for this?
Version 2.xlsm
GreetsPlease Login or Register to view this content.
Try this
Please Login or Register to view this content.
Not working :/ but thanks....
the problem is more with Bigger outcomes like 8.000,000 ...first dot and then comma really weird.
Hi,
obviously, in the data that you paste they have comma as thousands separator. So the first step in a "dirty" approach would be to remove it, and then replace the decimal dot with comma. However, based on the input data (e.g. if the input data come from csv ot text file) it would be better if you use import and specify that decimal separator is dot and comma is used for thousands separator.
If you are pleased with a member's answer then use the Star icon to rate it.
Where are they?Originally Posted by Shimazu
I don't find such string in col I...
As you see i merged your two same threads.
I'd like to ask for you to don't create duplicates threads.....
ON line 21 I on Sheet(FreshBooks)
if you look on the outcome on line 21 F on Sheet (output) it will give #valeu
And how can i do this.....(realy beginner here)
I21 is 8,264,46
If you run my code, the formula returns 8264.46 as it should.
OK it works now..but i still get #valeu on Sheet(output)
is there a way that Dot will chance to comma on J to ...intergrated on your code??
because of that line J there are still dot... i think that will fix the #Valeu
Are we looking at the same file?
I have no error after the code.
All looks fine.
Can you attach your file ??
.................
Ok whe are working in the same file.... but On Sheet (output) i still get #valeu
can i use your code double ?? then for line J in sheetFreshbooks)
How can i Edit this that will work on line J to?
Please Login or Register to view this content.
Last edited by Shimazu; 03-11-2014 at 04:48 AM.
Select the range that you want to apply then run.
Don't select entire column or Excel may crush.
Please Login or Register to view this content.
Its working now thank you
You should be very careful in asking/replying.
It was already "WORKING", you just didn't know how to run it.
And thanks for no repl.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks