Need Help in VBA UDF Function

Prasad K

Board Regular
Joined
Aug 4, 2021
Messages
180
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
here i have got a udf function of concat function and it's working perfectly in given range to combine text string & here what the problem is if the text string have extra space in cell range
and this udf function never trimming the extra space to single space

i have applied this udf function in excel 2016


VBA Code:
Function Concat(Rng As Range) As String
Dim cl As Range
Concat = ""
For Each cl In Rng
Concat = Concat & cl.Value & " "
Next cl
Concat = Trim(Concat)
End Function
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
Concat = Concat & Trim(cl.Value) & " "

But I think you have Excel's CONCAT() function in Excel 2016, so you should change the name of your UDF.
 

Prasad K

Board Regular
Joined
Aug 4, 2021
Messages
180
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
Concat = Concat & Trim(cl.Value) & " "
No Sir i don't have concat function in my excel 2016 that's why i applied this udf function

i have tried this line of code in my udf function

i getting #Value error


VBA Code:
Function Concat(Rng As Range) As String
Dim cl As Range
Concat = ""
For Each cl In Rng
Concat = Concat & cl.Value & " "
Next cl
Concat = Concat & Trim(cl.Value) & " "
End Function
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,990
Office Version
  1. 365
Platform
  1. Windows
Like this:

VBA Code:
Function Concat(Rng As Range) As String
   
    Dim cl As Range
   
    Concat = ""
    For Each cl In Rng
        Concat = Concat & Trim(cl.Value) & " "
    Next cl
    Concat = Trim(Concat)

End Function
 
Solution

Prasad K

Board Regular
Joined
Aug 4, 2021
Messages
180
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
Like this:

VBA Code:
Function Concat(Rng As Range) As String
  
    Dim cl As Range
  
    Concat = ""
    For Each cl In Rng
        Concat = Concat & Trim(cl.Value) & " "
    Next cl
    Concat = Trim(Concat)

End Function
Thank you so much
it's worked perfect
 

Forum statistics

Threads
1,175,663
Messages
5,898,756
Members
434,729
Latest member
Tej92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top