I am trying to use a trim formula to remove spaces that appear at the end of my numbers after copying from another report but the space is still there? Is it because I'm trying to trim a number? Everything is set to General - I even tried formatting to Text - but nothing is wokring. Please help...
Trying to trim "34.54 " to "34.54"
Thanks!
Last edited by jfs; 05-10-2011 at 11:47 AM.
Perhaps that is another character at the end...
try the CLEAN() function... and add 0 to convert to a real number...... e.g. =CLEAN(A1)+0
if not working then...
What does this formula yield? =Right(A1) where A1 is the cell containing the original value to trim.
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.
I tried the clean and it doesn't work. When I have the +0 I get the same #VALUE error. When I did the RIGHT formula, it showed up blank?
Sorry, I meant:
=Code(Right(A1))
The original cell reads "25 ", and when I entered that formula it now says 160?
Okay,
Try:
=SUBSTITUTE(A1,CHAR(160),"")+0
where A1 contains original.
It worked! My cell says 25 and has no space!
Ok, so obviously that's only one cell; I have a spreadsheet with probably 2000 numbers that need to be formatted the same way ... Do I have to do the CODE formula first and then do this one to get the right values to show up? And if so, can I replace the 160 in the second formula with a cell number?
Sorry for the 20 questions, I just don't know the formulas you used to heroically solve my problem.
If you want to replace the character in the cells in situ... then you can avoid formulas and use Edit|Replace.
Select the range to fix and do a CTRL+H, then in Find What box, hold the ALT key down and type 0160 using only the number pad of your keyboard. Leave the Replace with field blank and then click Replace All.
That should fix them all at once.
If you want the formula, then you only need to use the SUBSTITUTE formula if the cells are adjacent to each other, you can copy the formula down the column or across the row... and the A1 will adjust itself relatively.
I just used the replace to fix them all, you are fabulous!
Thanks for everything!
Great, you are welcome.
Please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
jfs 
Register To Reply
Bookmarks