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
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
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.
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.
Originally Posted by shg
Thank you!! - It works, simple and easy. I really appreciate it!!
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks