Hi! I have macro which adds text before number like this:
14,95 -> Norm. 14,95
It works, but if there's two numbers my code stops working
14,95-16,95 ..and nothing happens. How can I fix this?
Please Login or Register to view this content.
Hi! I have macro which adds text before number like this:
14,95 -> Norm. 14,95
It works, but if there's two numbers my code stops working
14,95-16,95 ..and nothing happens. How can I fix this?
Please Login or Register to view this content.
Not sure I understand what you mean when you say your code stops working and nothing happens.
If you have 14,95-16,95 in a cell, that's not a number, it's not two numbers, it's a text string made up of numbers, commas and a hyphen.
Maybe show all the code and, ideally, a sample workbook.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Because according to excel 14,95-16,95 is a Text Data
Do you want to keep 14,95 as real number or it can be converted as text data like Norm. 14,95. Because Adding Norm to a real number within the cell will make the number as text data.
Please confirm…
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
@Sixthsense: he's just changing the number format. But changing the number format for a cell with text in it, as we've both commented, won't have any effect.
Regards, TMS
@ TMShucks
Thanks for reminding me
Yes me too trying to saying the same as you described in your post.
At present OP is just adding the text by formatting which will work when the data is Number.
But as we know it won’t apply the prefix text which is added in formatting if the datas is text.
So if we add the Prefix Norm. by default to all cells (By way of looping through all cells) then the number value 14,95 will be considered as Text data.
So that’s why I am asking the OP whether the 14,95 can be converted as text by adding the Prefix WITHIN the cell.
Hi and thank you for your replies. I'm sorry I couldn't explain my problem properly, but I attached two worksheets to explain my problem. In example.xlsm you can see how my macro works and in result.xlsm you can see how want it to work. It doesn't matter to me whether the cells end up text data or numbers, I just want the result as shown in result.xlsm
example.xlsm
result.xlsm
try
Please Login or Register to view this content.
One way:
Please Login or Register to view this content.
Regards, TMS
Thanks jindon and TMShucks! Those both work!
As I was testing those with some different numbers, I noticed that if the value of the cell is for example 7,90, it ends up 7,9. Or 0,50 becomes 0,5. Or 25,00 becomes 25. I would like to have two decimals in numbers all the times. Is that possible?
EDIT:
Here's example:
example2_.xlsm
EDIT2: Working example is example2_.xlsm
Last edited by Hercules33; 11-12-2013 at 04:36 AM.
Please Login or Register to view this content.
Regards, TMS
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
This will not convert the cells that are already converted.
See attached.
Please Login or Register to view this content.
If that is the case then why not simply like this??
Even the below code wont touch the cells which is previously showing as "Norm. "0.00 when the cell data is real number.
Slight Change in TMShucks code
Please Login or Register to view this content.
@Sixthsense: thanks for providing the modification.
Regards, TMS
Thanks for the rep.
If you are satisfied with the solution(s) provided, please mark your thread as Solved. See post #11.
Regards, TMS
You guys are totally misreading.
It will not convert the cells that are properly converted.
Norm. 14,95
Norm. 1,95-2,50
But it will still correct the cells like
Norm. 6.5 to Norm. 6,50
Norm. 32.5-40.5 to Norm. 32,50-40,50
etc.
Last edited by jindon; 11-12-2013 at 06:54 AM.
I have to say NO in this case!
Want to know WHY? Read the below quoted content.
The above words of OP clearly indicates that OP’s system setting has been replaced with comma for decimal places.
Because the OP might have replaced the dot as comma in his/her control panel settings.
As per OP’s confirmation the 14,95 is getting changed as Norm. 14,95 with "Norm. ""0.00" formatting, so the comma should be considered as decimal place.
If you see the post #1 contents then you will realize that the OP’s problem is not about formatting the decimal place of the number. It is all about adding the prefix to the Data. Adding the prefix via formatting to a number will work. But adding prefix to a text data via formatting won’t work as expected.
So based on all these in depth analysis we just gone for a simple looping system instead of any complicated approach.
The OP only knows.
Thanks for the rep, to OP anyway.
Not too complicated for me...Originally Posted by :) Sixthsense :)
Last edited by jindon; 11-12-2013 at 07:33 AM.
Why not just use number format:
So your line of code becomes:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Olly; 11-12-2013 at 07:33 AM.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
@OllyXLS: the whole point of this thread is that the OP does not always have numbers in the cells, he has a mixture of digits, commas and hyphens that constitute text. Hence applying a number format to it will not work.
@jindon: I am always impressed when you and others use Regular Expressions to find and change things. Truly impressed. Sadly, I can look at the code and the regular expressions forever and it simply does not sink in. I think I'm going to have to practise.
I agree with Sixthsense that, at face value, the simple loop seems to do the trick. That said, I can see how your approach has a benefit in formatting the numbers if there are more than one. Looks as though you're changing the decimal separator though.
Regards, TMS
@TMShucks
Can't test my code as comma is not a decimal separator. However I guess it is working somehow as OP attached my code in the latest file.
Anyway, it might be better to take Text property insteda of Value property, I guess.
Please Login or Register to view this content.
@OllyXLS: I stand corrected. You are absolutely right. Please accept my apologies.
@Jindon: looks like there is a much simpler solution!
Regards, TMS
Thank you all, I got it now and I think I learned something too
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks