It includes a column which gives me data such as 12,456.78 USD
I'm trying to convert it into values that I can use, but what's bugging me is the space between the number and the currency. I can't seem to shift it.
I know it's code(160) but I can't seem to get rid of it using either find and replace or Text to columns.
In the words of Donny Osmond...Someone help me...help...help me...help me please!
sorry....
In an adjacent column...
=SUBSTITUTE(A1,CHAR(160)," ") where A1 contains original string, copied down
replace Char(160) with a regular space....
or use "" instead of " " if you want no space.
You can then copy this column and Edit|Paste Special >> Values over original... then delete formula column
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
NBVC
You bloody hero you!
Thank you so much
tim
for future reference heres a modified answer i gave in a previous thread
its actualy code 0160
you can easily replace it
alt+f to find /replace as usual
in the search field
hold down 'alt' and type in 0160 (using the number keypad)
release alt
you will see the cursor has moved 1 space to the right
in the replace field just enter 1 space using the space bar
replace all
I did try that as I was going to suggest the same thing, but I get an error that Excel hasn't found matches.....Originally Posted by martindwilson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks