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

1. ## Convert text to number in Excel 2007/Win7 Pro

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.  Reply With Quote

2. 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.  Reply With Quote

3. Originally Posted by Simi 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.  Reply With Quote