Hi,
I hope you all are well.
I am facing extra space problem with my data copied from mail text body.
I did many things like trim,substitute & replace. only replace function work but unable to fix num char.
Sample file is attached for your needful action pls.
Thanks
Ankur
Try,
Q2: =SUBSTITUTE($N2,CHAR(160),"")
or in N2,
=IFERROR($J2*SUBSTITUTE($N2,CHAR(160),""),"")
and drag/copy down.
Best,
berlan
Last edited by berlan; 08-14-2015 at 03:09 AM.
the blank space is Code 160
so formula
Formula:
Please Login or Register to view this content.
or find and replace with CTRL+H method
CTRL+H to display Find/Replace dialog
Find: ALT+160 (Hold the ALT key and enter numbers via numeric keypad)
Replace: <leave blank>
Last edited by humdingaling; 08-14-2015 at 03:09 AM. Reason: double post
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Try
=SUBSTITUTE(N2,CHAR(160),"")+0 (+0 to convert it back to a number)
Add If error if required
=IFERROR(SUBSTITUTE(N2,CHAR(160),"")+0,0)
Cheers!
Deep Dave
Thank you all. But i was using " " instead of Char(160). Why Substitute function didn't work??
Last edited by shukla.ankur281190; 08-14-2015 at 03:18 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks