+ Reply to Thread
Results 1 to 6 of 6

Convert Imported Text Containing Dollar Sign to Numbers

  1. #1
    Registered User
    Join Date
    11-03-2019
    Location
    midwest
    MS-Off Ver
    latest
    Posts
    2

    Convert Imported Text Containing Dollar Sign to Numbers

    Hi, I'm usually pretty adapt at Excel, but I can't figure this out and so hope someone can help!

    I am copying a set of numbers from a website that when I past them in excel they populate in one column. The numbers are not able to be summed in their format (they are money, and the format is $ 16.00).

    What I've been doing is manually writing the same number in the cell just to the right of the number.

    When I do this, after 3-4 cells, Excel will show me the rest of the numbers - as though it was filling them in for me. My goal is to get it to do that, but I can't figure out how to 'grab' those numbers and have excel leave them. What happens inevitably is whatever I do, excel does not keep the numbers that it was showing me, and I have to manually type all of them.

    I hope that makes sense without a picture. What I'm trying to do is get the numbers that are copied to sum in an easy way. It seems like there is an easy Excel function that knows what I'm doing and can do it for me, but I don't know why those numbers that it is showing me disappear or how to keep them.
    Last edited by AliGW; 11-03-2019 at 11:06 AM.

  2. #2
    Valued Forum Contributor Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,171

    Re: Autofill numbers

    Use a search and replace to remove the dollarsign. Then the texts should convert to numbers

    You can also use text to column option in data menu to reformat the collected texts to values.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,618

    Re: Autofill numbers

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As yo are dew, I shall do it for you this time.)

    Administrative Note:

    Please update your forum profile with the correct 'latest' version - either Excel 365 or Excel 2019 - they are not quite the same.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Registered User
    Join Date
    11-03-2019
    Location
    midwest
    MS-Off Ver
    latest
    Posts
    2

    Re: Autofill numbers

    Thank you moderator for helping with the title.

    Roel - thank you for that. It is a dollar sign plus spaces.

    My question is though that Excel actually is autofilling the numbers in the new column for me. They show up after I type 2-3 of them. They are a lighter font, so it seems that Excel is 'suggesting' that it autofill, exactly as I want it. It autofills the new column with the exact numbers I want in number format (again, in a lighter font, it seems it is suggesting this change).

    It seems like a very useful function and much quicker than search and replace. However, I do not know how to get the program to keep what look like suggested autofill numbers. I'm sure it's as easy as pressing a certain key. They don't autofill if I wait, if I press 'enter', if I continue to type the numbers, etc. All of those cause the suggested numbers to go away.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,618

    Re: Convert Imported Text Containing Dollar Sign to Numbers

    I think you missed this:

    Administrative Note:

    Please update your forum profile with the correct 'latest' version - either Excel 365 or Excel 2019 - they are not quite the same.

    I think you would be better-served finding a way to import the numbers in a suitable format rather than trying to process them in this way. Which website are you copying from?

  6. #6
    Valued Forum Contributor Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    1,171

    Re: Convert Imported Text Containing Dollar Sign to Numbers

    You might want to check this article on autocomplete
    https://excelribbon.tips.net/T006262...oComplete.html

    That is closer to what you are describing. Check the settings to make sure it works..

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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