Convert text to number in Excel 2007/Win7 Pro

NVMOS007

New member
Joined
Jan 22, 2014
Messages
2
Reaction score
0
Points
0
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.
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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.
 

NVMOS007

New member
Joined
Jan 22, 2014
Messages
2
Reaction score
0
Points
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.

Works great-this is why I love the internet.
Thanks for your assistance
 
Top