Split Names (First, Middle [If Available] and Last) with One Single UDF in VBA

Kartick0075

New Member
Joined
Jan 9, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
Dear Respected Everyone,
I've come here with a request to solve this problem. I'm a newbie in VBA and learning it for the last few months. But, I got stuck in splitting Names (First, Middle [If Available] and Last) with One Single UDF Function in VBA. I successfully did it with Sub Procedure. But, I failed to do it with One SIngle UDF. I can't build logic to successfully achieve my purpose with One Single UDF Function. With my current VBA skills, It requires 3 UDF Functions (Each Function for Each Part of Name) to serve my purpose. I generally prefer Inbuilt or UDF Functions to Macros as they (Inbuilt or UDF Functions) are much more flexible. I mean to say, creating function once and use them anywhere in that workbook. Please help me...

I've given my Macro and incomplete UDF Code below:

=====================================================
Module: (CodeName:= Sheet1(SheetName:= Split Name))
------------------------------------------------------------------------------------------------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).Row

If Application.Intersect(Range("B3:B" & LastRow), Target) Is Nothing Then
Rem Nothing is needed
Else
Call SplitNameViaSubProcedure
End If
End Sub
------------------------------------------------------------------------------------------------------------
Module: Generic (ModuleName:= Split_Name)
------------------------------------------------------------------------------------------------------------
Option Explicit
Option Base 1

Sub SplitNameViaSubProcedure()
Application.EnableEvents = False

Dim OuterLoop As Long
Dim InnerLoop As Long

Dim FirstRow As Long
FirstRow = Sheet1.Range("F3").Row

Dim LastRow As Long
LastRow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row - 2

Sheet1.Range("F" & FirstRow & ":" & "H" & Rows.Count).ClearContents

For OuterLoop = 1 To LastRow
Dim NameArray() As String
NameArray = Strings.Split(Expression:=Sheet1.Range("B" & OuterLoop + 2).Value, Delimiter:=" ")

For InnerLoop = 1 To UBound(NameArray) + 1
If UBound(NameArray) + 1 = 3 Then
Sheet1.Cells(OuterLoop + 2, InnerLoop + 5).Value = NameArray(InnerLoop - 1)
Else
Sheet1.Range("F" & OuterLoop + 2).Value = NameArray(InnerLoop - 1)
Sheet1.Range("H" & OuterLoop + 2).Value = NameArray(InnerLoop)
Exit For
End If
Next InnerLoop
Next OuterLoop

Application.EnableEvents = True
End Sub
=====================================================
But, I can't achieve it with One Single UDF. I'm also sharing this incomplete code...
=====================================================
Option Explicit
Option Base 1

Function SplitNameViaUDF(InputRange As Range) As String
Dim NameArray() As String
NameArray = Strings.Split(Expression:=InputRange.Value, Delimiter:=" ")

SplitNameViaUDF = NameArray(0)
End Function
=====================================================
 

Attachments

  • Split Names with UDF.png
    Split Names with UDF.png
    42.8 KB · Views: 6

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
Excel Formula:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range, v As Variant
    If Not Intersect(Range("B3:B" & Rows.Count), Target) Is Nothing Then
        For Each cell In Intersect(Range("B3:B" & Rows.Count), Target)
            If cell.Value = Empty Then
                cell.EntireRow.Range("I1:K1").ClearContents
            Else
                v = Split(cell.Value)
                Select Case UBound(v)
                    Case (0)
                        cell.EntireRow.Range("I1").Value = v
                    Case (1)
                        cell.EntireRow.Range("I1").Value = v(0)
                        cell.EntireRow.Range("K1").Value = v(1)
                    Case Else
                        cell.EntireRow.Range("I1:K1").Value = v
                End Select
            End If
        Next cell
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,239
Messages
5,595,002
Members
413,958
Latest member
SBM

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