Hi,
I have some text that also contains numbers with a decimal point. I am trying to remove all the digits from the text using the replace function. Although the entire data range is formatted as text, I cannot remove the digit just left of the decimal point. If it's a 0, nothing happens, if it's another digit it is changed into 0.
I am using a macro, but I have the same problem when working directly with Excel's functions.
What should I do?
Thanks,
Chen
Last edited by chengafni; 10-01-2017 at 06:29 AM. Reason: the scope of the problem is more general than defined initially
Post a sample of your workbook for analysis. Include also your VBA attempt.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Ok, take a look at the sample data.
Please try the following:
- Replace all 3s with nothing: 3 in the first cell is changed into 0.
- Replace all 0s with nothing: Excel announces that 2 replacements have been made, in reality nothing happens.
- Replace all a's with nothing: Excel announces a problem with a formula and leaves the 'a' in the last cell.
The code in the attached file produces the same results.
Last edited by chengafni; 10-01-2017 at 06:29 AM. Reason: the scope of the problem is more general than defined initially
When I run the code, I get the following. What are your expected results supposed to be?
Data Range
I got the same results. The desired results would be:
Row 1: .5
Row 2: .441628
Row 8: =>
1) should be vbNullString
Try
Please Login or Register to view this content.
Thank you and sorry about the typo in the code.
This is not exactly what I had in mind. I am trying to remove characters one at a time, and I want Excel to really treat EVERYTHING as text. I don't understand why formatting cells as text doesn't achieve that.
Try formula
Enter in B1 and copy down
Formula:
Please Login or Register to view this content.
v A B 1 3.5 .5 2 0.44162777 .441628 3 a 4 -19 5 v 6 1948 7 c 8 a=> =>
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Because excel cell has "auto-casting" functionality, so as soon as refreshed by the Replace method, the cell recognise it as NUMBER no matter how you change the format.Originally Posted by chengafni
Also = sign at the beginning.
It will tread as a Formula so can't be replaced.
Thank you all, I really appreciate your willingness and effort.
Your proposals are rather specific to the examples I provided. I am looking for a global solution that will enable me to remove specific characters from an entire range. From your responses I get that there isn't one.
You are welcome and good luck in finding the solution with REPLACE method in years...
Hmm... You want to remove "specific characters" but you never mentioned what those characters are? Can you list those very special characters?Thank you all, I really appreciate your willingness and effort.
Your proposals are rather specific to the examples I provided. I am looking for a global solution that will enable me to remove specific characters from an entire range. From your responses I get that there isn't one.
well, I have a very long text that contains all sorts of irrelevant characters. I have a list of about 30 characters (or basically any list that the user wishes). I wrote a macro that is supposed to delete all but those 30 characters from the text. It works most of the time, but fails in specific cases as those mentioned above.
Interestingly, when a character happens to appear in a string that Excel identifies as a formula (e.g., a in a=>), trying to remove the character (e.g., a) leads to an "error in formula" and as a result Excel stops the replacing process and leaves all the remaining instances of the character untouched.
The only logic I see here is that you want this special formatting to be applied only to the numbers with decimals and everything else with an exception of "=>" to be ignored.
Try this modified formula:
Formula:Please Login or Register to view this content.
or a little bit shorter
Formula:Please Login or Register to view this content.
Last edited by AlKey; 10-01-2017 at 12:29 PM.
Ok, thank you very much!
Hi,
You might also use an array instead of the Range.Replace method
Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
Yes, that does the job. Thank you very much! (and also for solving the other thread).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks