UDF Function

Hi Experts,

Will Anyone Help me with UDF VBA Function  code which convert text number to number.my data  have leading and trailing spaces (Spaces are not identical in characters count)so i am looking for UDF function like =Mynumber(Cell) i.e. it will remove all leading and trailing spaces.

Thanks
LVL 8
Naresh PatelTraderAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Try this...

In G1
=IF(F1="","",SUBSTITUTE(F1,CHAR(160),"")+0)

Open in new window

and then copy down.
0
 
ProfessorJimJamCommented:
there is no need for UDF. built in function

use =trim(cell)+0
0
 
Naresh PatelTraderAuthor Commented:
i tried that but "+0" i dint tried.....+0 Stand for?

Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ProfessorJimJamCommented:
when you do a math operation it will covert it to real number if the number was stored somehow in text format.

so you need to do +0  or *1  any of these can do the job.
0
 
Naresh PatelTraderAuthor Commented:
it wont see attached.

Thanks
Functions-For-Format.xlsx
0
 
ProfessorJimJamConnect With a Mentor Commented:
please find attached.


=IFERROR(IF(F1<>"",SUBSTITUTE(F1,CHAR(160),""),"")+0,"")
Functions-For-Format.xlsx
0
 
Naresh PatelTraderAuthor Commented:
Both Are Formula is Working ...what is the difference between each other ?

Thanks
0
 
ProfessorJimJamCommented:
Neeraj formula's is better. does not need the extra IFERROR
1
 
Naresh PatelTraderAuthor Commented:
Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad we could help.
0
All Courses

From novice to tech pro — start learning today.