In column A2 trying to remove trailing spaces , commas, the word USD and convert cell to number
How to merge the formulas below as one.
I have to do it in different cells
USD 11,269.44
expected RESULTS 11269.44
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,",",""))," "," ")
=RIGHT(A2,LEN(A2)-3)
Last edited by JEAN1972; 09-06-2017 at 03:40 AM.
hi JEAN1972. you don't have to remove the comma if you convert it to a number format. this is how you can do that
=--MID(A2,FIND(" ",A2)+1,LEN(A1))
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
OR,
try the following:
=1*SUBSTITUTE(A1,"USD","")
Just in case there are NOT always 3 letters at the start of the string, use this more generic solution:
=ABS(LOOKUP(10^10,--RIGHT(A2,ROW($1:$10))))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Removed By AS
Last edited by shukla.ankur281190; 09-06-2017 at 04:25 AM.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
benishiryo,cbatrody,Glenn thank for the solutions , worked as requested .
shukla.some issues with formula , does not remove usd, spaces, commas , but convert to pounds sterling currency
Removed By AS
Last edited by shukla.ankur281190; 09-06-2017 at 04:25 AM.
You're welcome and thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks