+ Reply to Thread
Results 1 to 8 of 8

Text to numbers

  1. #1
    Registered User
    Join Date
    03-17-2020
    Location
    Northwood UK
    MS-Off Ver
    2013
    Posts
    3

    Text to numbers

    I know everything on line tells me its easy but it is just not happening for me.
    I have a column of values that have come down from a statement and I have converted the cvs file to Excel and now got a column of text. I just cannot get these to become numbers . Can anyone help me.
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Text to numbers

    Hello Casata & Welcome to the Forum,

    I'm guessing you are looking for the ' to be replaced with a .

    Try in B4 copied down

    =SUBSTITUTE(A4,"'",".")+0
    HTH
    Regards, Jeff

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Text to numbers

    not sure what numbers you are looking for but if Jeff's suggestion isn't what you want, you can simply do a find and replace...
    find what '
    replace with [leave blank]
    then replace all and it should work for you.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    03-17-2020
    Location
    Northwood UK
    MS-Off Ver
    2013
    Posts
    3

    Re: Text to numbers

    Thank you jefferybrown. That seems to work although not at all the answer I was expecting.
    The figures on the sample spreadsheet I attached are all text and its not so much the ' that I was troubled with, it was the fact I needed numerical values , not text. But your advice worked - so thank you very much. I spent hours messing about with this problem on advice from the net- but nothing else worked.

  5. #5
    Registered User
    Join Date
    03-17-2020
    Location
    Northwood UK
    MS-Off Ver
    2013
    Posts
    3

    Re: Text to numbers

    Sorry Sambo kid but I didn't even know how to do what you suggest. However as you see above I think I've got it sorted.
    Thank you for your reply.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Text to numbers

    Well sure glad you now have the solution you were searching for. You are very welcome and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  7. #7
    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: Text to numbers

    Also, you can proceed with this one

    Enter formula in B4 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B
    1 Trans. value
    2
    3
    4 24'221 24221
    5 16'643 16643
    6 13'611 13611
    7 9'512 9512
    8 -16'661 -16661
    9
    10
    11
    12 20'027 20027
    13 24'220 24220
    14 -33'710 -33710
    15 -24'739 -24739
    16 -27'809 -27809
    17 21'035 21035
    18 3'334 3334
    19 -23'668 -23668
    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

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Text to numbers

    for my suggestion, first (I believe your data was in col A), highlight the whole column.

    Go to the home tab at the top of the workbook, over toward the right is a section called editing and in it are AutoSum, Fill, Clear, AZ and a filter and under it says Sort & Filter and beside that is what could be described as a magnifying glass with Find & Select under it. If you click on the Find & Select down arrow it will open a box with Find, Replace, Go To etc. Then you click on Replace and it opens a box with a box beside Find what: and you put ' in that box, under it in the Replace with: you leave it blank.

    Then hit replace and it will replace the first one it finds, if you like the results then hit replace all, or just hit replace all and if you don't like the results just hit the undo function. It is faster and easier than a formula IMO.

+ 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. Extract Text from Numbers Where Text and Numbers are Different # of Characters
    By EvolutionJulie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2019, 10:50 PM
  2. VBA to convert a range from numbers to numbers stored as text (Text to Columns)
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-06-2017, 05:50 PM
  3. Replies: 11
    Last Post: 04-10-2017, 06:23 AM
  4. Replies: 4
    Last Post: 10-11-2016, 01:51 PM
  5. Replies: 19
    Last Post: 07-20-2014, 04:55 PM
  6. [SOLVED] Marco to convert numbers stored as text to numbers and dates stored as text to numbers
    By a2424 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-22-2014, 10:19 AM
  7. [SOLVED] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 AM

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