Results 1 to 3 of 3

Thread: Convert text to number in Excel 2007/Win7 Pro

  1. #1

    Convert text to number in Excel 2007/Win7 Pro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have the following cell content: C $67.99 (there is a whole column of this type of content, approximately 300 items)
    This cell is General formatted and its contents are left aligned in Excel.
    How does one strip the C, the space after the C and the $? (The C denotes Canadian $)
    I need to be left with a number which I can then include in a sum
    This action then needs to be performed on all the items in the same column

    PS - I obtain the initial data from eBay in .csv format. I download the file, open it as .csv in Excel and then save it as a workbook. I re-open the workbook and proceed from there.

    Any help would be appreciated. I've chased after eBay inquiring why this data is in this format-no response.

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    if you simply take the C off of the front of the line you can convert it to a number or currency. if it is always "C $" and you only want the numbers left you could use the following in B1.
    =RIGHT(A1,LEN(A1)-3)+0
    This will take off the "C $". if you change it to =RIGHT(A1,LEN(A1)-1)+0 It will just take off the "C" and should still convert to currency.
    How this works, it takes the right x amount of characters from the string in A1. x is figured by the entire length of the string in A1 - 3 or 1 in the examples above.
    the +0 at the end takes the string of now numbers and adds a number to it, thus converting the string to a number format.

    Hope this helps you.

  3. #3
    Quote Originally Posted by Simi View Post
    if you simply take the C off of the front of the line you can convert it to a number or currency. if it is always "C $" and you only want the numbers left you could use the following in B1.
    =RIGHT(A1,LEN(A1)-3)+0
    This will take off the "C $". if you change it to =RIGHT(A1,LEN(A1)-1)+0 It will just take off the "C" and should still convert to currency.
    How this works, it takes the right x amount of characters from the string in A1. x is figured by the entire length of the string in A1 - 3 or 1 in the examples above.
    the +0 at the end takes the string of now numbers and adds a number to it, thus converting the string to a number format.

    Hope this helps you.
    Works great-this is why I love the internet.
    Thanks for your assistance

Posting Permissions

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