[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

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
0
Naresh Patel
Asked:
Naresh Patel
  • 4
  • 4
  • 2
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Naresh PatelTraderAuthor Commented:
it wont see attached.

Thanks
Functions-For-Format.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this...

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

Open in new window

and then copy down.
0
 
ProfessorJimJamCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now