Hi all,
I have a cell with the contents "7 : 1"
I then have two lovely formula in the cells below:
=LEFT(C45, SEARCH(":",C45,1)-1)
=RIGHT(C45,LEN(C45)-SEARCH(":",C45,SEARCH(":",C45)-1))
What I need is for the formula to return the values "7" and "1"
What I am getting is "7 " and " 1".
How can I alter the formula to remove the space that is being returned before/after the number.
NOTE: this needs to work for when the 7 is double or triple digits. ie. "13 : 2" or "781 : 1"
Thanks
hi there. wrap your formula with TRIM
=TRIM(RIGHT(C45,LEN(C45)-SEARCH(":",C45,SEARCH(":",C45)-1)))
or:
=RIGHT(C45,LEN(C45)-SEARCH(":",C45)-1)
or:
=MID(C45,FIND(": ",C45)+2,LEN(C45))
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks