+ Reply to Thread
Results 1 to 5 of 5

Convert text to numbers

  1. #1
    Registered User
    Join Date
    05-14-2012
    Location
    Spring Valley, NY
    MS-Off Ver
    Excel 2010
    Posts
    27

    Convert text to numbers

    Hi, please see the attached spreadsheet. I am struggling converting the cell (column B) that has dollar figures into a number. I tried many things but it's not letting me change this to numeric.

    Any ideas? Please see the attachment

    Thank you,

    YR
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Convert text to numbers

    There are spaces (technically CHAR(160)'s that look like spaces) after the numbers.

    To remove them, you can use this in C3:
    =SUBSTITUTE(B3,CHAR(160),"")+0
    Drag formula down column C.

    Now copy/paste values and format as desired.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,564

    Re: Convert text to numbers

    There appear to be a few space type characters at the end of each number (select one of the cells, press F2 to enter edit mode, then move the cursor to the end of the text string). I entered =CODE(RIGHT(B3,1)) and got a 160 -- indicating a non-breaking space character at the end.

    I suggest you review the information here (https://support.office.com/en-us/art...rs=en-US&ad=US ) about cleaning these higher code number space characters from your numbers. They recommend using the SUBSTITUTE() function to replace the 160 space character with something CLEAN() will recognize -- then you can use CLEAN() to remove the spaces, which will allow you to convert the text to numbers.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    05-14-2012
    Location
    Spring Valley, NY
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Convert text to numbers

    Thank you!! - It works, simple and easy. I really appreciate it!!

  5. #5
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Convert text to numbers

    Hi,

    You can do as 63falcondude recommended in Post #2, Or:

    1. Select one of the cells from B3:B10, say B3
    2. Click within the formula bar, Select a Single "SPACE", right click, Copy
    3. Hit Shift and F5
    4. Select "Replace" tab, within "Find what" box, Paste in your copied "SPACE"
    5. Click "Replace All"

    Now your Column B (and elsewhere within the sheet, if any) values are converted to Real Numbers without the CHAR(160)-SPACES

+ 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. 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
  2. [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
  3. [SOLVED] Convert numbers stored as text to numbers for range of data that changes
    By excel2425 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2014, 01:57 PM
  4. [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
  5. [SOLVED] How do you apply the int() in VBA in order to convert numbers stored as text to numbers
    By djaurit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2012, 02:57 PM
  6. Replies: 2
    Last Post: 10-22-2009, 02:50 PM
  7. [SOLVED] Convert numbers stored as text to numbers Excel 2000
    By Darlene in forum Excel General
    Replies: 6
    Last Post: 01-31-2006, 04:10 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