+ Reply to Thread
Results 1 to 22 of 22

Find and Replace Issue

  1. #1
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Find and Replace Issue

    Hello Everyone,

    I have recently discovered an issue with the find and replace function. I do not know if it is an issue with my system, but it has me completely stumped. I am trying to convert a column of dollar amounts to their plain number equivalent. For example, $1,500.00 to 1500. So to do this, the easiest solution I can think to do is to find a replace the "$" with nothing, find and replace the "," with nothing, and find and replace the ".00" with nothing. When I do find and replace for the ".00", it will ONLY remove the .00 from my dollar amounts that are over $1,000. So anything less then $1,000, such as $750.00, it will not remove the .00. Below I have included step by step screenshots to help illustrate the problem. Like I stated, I have no idea how to fix this and any help would be greatly appreciated.

    Conversion issue 1.JPG


    Conversion issue 2.JPG


    Conversion issue 3.JPG


    Conversion issue 4.JPG
    Last edited by joshuarobbins; 12-23-2015 at 05:05 PM. Reason: Another issue. Not solved.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find and Replace Issue

    I suspect that what you really want to do is change the number format that is displayed for numbers.
    Try this:
    • Select the range of numbers to be impacted.
    • Home tab....Number Format section:
    Select: General from the dropdown list
    Adjust displayed decimals if necessary

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find and Replace Issue

    Your screen caps would suggest that it is down to formatting, but if it was then find and replace wouldn't remove the $ prefixes?

    Try selecting the whole column and changing the cell format to 'General', does that solve it?

  4. #4
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Hello Ron and thank you for your response. Unfortunately I have tried to do that, and it does not make any changes to the cells at all. What I guess I am REALLY trying to do is get the amount in the formula bar to be plain text. So for example, I want the formula bar to display 750 versus $750.00.

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find and Replace Issue

    Try this:
    • Select the range of numbers to be impacted.
    • Data.Text-to-columns....click: Finish

    Does that help?

  6. #6
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Hey Jason,

    Changing the entire column to general did not do it either. It still will not find and replace for ".00" for any amount lower then 1000. Is it a placeholder issue? I just don't get the difference between a value greater then 1000 and lesser then 1000.

  7. #7
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Hello Ron,

    That did not seem to work either. I get the below error:

    Conversion issue 5.JPG

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find and Replace Issue

    Select a single problem cell.

    Is that cell formatted as 'General'?

    With the problem cell selected, press Alt h 9

    Does the number of decimal digits reduce?

    Double click the cell to enter edit mode, is the actual value in the cell the same as the visible formatted value?

    If not, what is actually in the cell?

  9. #9
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find and Replace Issue

    I'm confused....Text-to-columns doesn't perform a find/replace.
    Can you post a sample workbook so we can see what you're dealing with?

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Find and Replace Issue

    There is another thing to consider. The numbers were formatted through Conditional Formatting and it can only be changed by removing it.
    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

  11. #11
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Hello Jason,

    Reducing the decimal does not do anything for some reason. So even though the amount in the problem cell is 750.00, when I decrease the decimal, it does not do anything. And the cells are formatted as "general".

  12. #12
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Attached should be a sample workbook for you all to see the issue I am referring to.

    Sample Book.xlsx

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find and Replace Issue

    There is a hidden character in there causing the problem.

    Try find and replace using .?00 as the find criteria.

  14. #14
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find and Replace Issue

    Thanks, that's a big help.
    As Jason mentioned, there's a non-displaying character in some of the "numbers"

    You could use find/replace
    or...if you want no digits after the decimal place at all
    • Select the single-column of cells to be impacted
    • Data.Text-to-columns
    ...Check: Delimited.......Click: Next
    ...Check: Other Type a decimal point in the box
    ...Click: Next
    ...Select the second column....Check: Do not import column (skip)
    ...Click: Finish

  15. #15
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Wow, that did it! How on earth did you figure that out? I'd love to know for future reference.

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find and Replace Issue

    Used the LEN() function to check the number of characters in the cell, which showed as 1 more than expected, given that replacing .00 didn't work, it was a fair assumption that the 'extra' character must have been somewhere in the middle of the string you were trying to replace.

    Entering edit mode in the cell, then using the cursor keys to move through the characters was the simple way to find it, it appeared as a very narrow space so had to press cursor twice to move over it.

    Strange part is, the hidden character returned code 63, which is the conventional question mark, so curious why it didn't show in the cell.

  17. #17
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Well let me ask this then. Is there a way to get rid of these "hidden" characters? Because this is actually messing up the Spreadsheet when I go to save it as a CSV, comma delimited.
    Last edited by joshuarobbins; 12-23-2015 at 05:02 PM.

  18. #18
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find and Replace Issue

    I don't think that there is an easy fix. If your data is being imported from another source then probably best to look at ways of fixing it before, or as part of the import process.

  19. #19
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    Yeah, the data is being imported from another source, and this did not used to be a problem. There must have been an update I suppose. With that being said, is there anything I can do fix it when importing the data? Literally all I do is copy and paste the data from a monthly report. It is copied and pasted from firefox and/or internet explorer.

  20. #20
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find and Replace Issue

    With copy and paste import, all you can do is find and replace. The problem you will have is that the stray character is not easily identifiable.

    Checking the character code in excel returned 63, which, as I mentioned earlier, is the code for '?' however searching for '~?' yields no results (the preceding tilde ~ character is used to tell excel to search for an actual question mark instead of treating it as a wildcard character).

    My guess would be that it is part of a character set which is not recognised by excel, but that is beyond my understanding, maybe someone else will know more.

  21. #21
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: Find and Replace Issue

    I see exactly what you mean. After I convert it to a CSV file, almost every column has the mysterious "?", but when I do a find and replace for ~?, it does not remove the question mark. You have been extremely helpful Jason, thank you.

  22. #22
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Find and Replace Issue

    Looked into it a bit more and found that the problem character is a Unicode zero width space, which, theoretically you should be able to remove using the following.

    Please Login or Register  to view this content.
    But for whatever reason, it doesn't work.

    Also, I've noticed that it exists in more most, if not all of the cells in column A, not just those where you couldn't remove the decimal value earlier. My guess is that some had the hidden character before the period, which is why they replaced correctly.

    The process can be done manually, but even doing that with the macro recorder running yields nothing useful.

    The manual process.

    Enter edit mode in a known problem cell (i.e. double click the cell so that the cursor is active in the text).

    Use the left / right cursor keys to move through the text until you find the hidden character (this will be when you press the cursor key, but the cursor on screen doesn't visibly move to the next character).

    Now hold down the shift key and press (only once!) the arrow in the opposite direction to the last one you pressed.

    If you have done it correctly you will have highlighted the invisible / zero width character in the cell. Note that as it is zero width you will not be able to see the highlighted character, if any of the characters in the cell are visibly highlighted then it has gone wrong, time to start again, do not pass go, do not collect $200

    If you did it successfully, press ctrl c to copy (don't try using right click menus cos that can kill the highlighting and you will have to start again), are you seeing a pattern yet? Something that should be simple is becoming anything but!

    Next press ctrl h.

    Make sure that the 'find' box is completely empty by using delete and backspace to remove any invisible characters left over from previous efforts.

    Paste the zero width character into the find box (any method should work this time).

    Check that the 'Replace With' box is also empty, then hit 'Replace All'.

    Make a list of the offensive things you would like to say to whoever added the zero width characters to the data in the first place.

    Job done.
    Last edited by jason.b75; 12-29-2015 at 07:17 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 16
    Last Post: 12-02-2014, 03:38 PM
  2. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  3. Replies: 3
    Last Post: 03-04-2014, 01:47 PM
  4. [SOLVED] Issue with FIND/REPLACE Excel 2003 VBA
    By dawatcher in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2013, 06:44 PM
  5. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  6. Excel 2007 : 2007 Find and Replace Issue
    By jeremy3 in forum Excel General
    Replies: 3
    Last Post: 02-24-2009, 08:28 PM
  7. [SOLVED] Excel find and replace issue
    By buddyorliz in forum Excel General
    Replies: 3
    Last Post: 06-04-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1