vba help - extract last name

[Mallesh23]

Hi Team,

How to extract Last Name/Last Word from Column A. Space is the seperator between Names.
if Single Name then it will be Last Name if blank then column B should be dash.

Below is a table with expected output.

Book47
	A	B
1	Name	Expected Last Name
2	Sachin Tendulkar	Tendulkar
3	M S Dhoni	Dhoni
4		-
5	Virat Kohli	Kohli
6	Rahul	Rahul
7	Chris Gayle	Gayle
Sheet1

Thanks
mg

 

[mole999]

simple search returns > Excel formula: Get last name from name | Exceljet

 

[Fluff]

Another option

+Fluff New.xlsm
	A	B
1	Name	Expected Last Name
2	Sachin Tendulkar	Tendulkar
3	M S Dhoni	Dhoni
4		-
5	Virat Kohli	Kohli
6	Rahul	Rahul
7	Chris Gayle	Gayle
8
Main

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2="","-",TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)))

 

[Mallesh23]

Hi Fluff and Mole,

Thank you for your help, formula worked in excel.
I tried using below code This method is also worked.

Sub LastName(ByVal ws As Worksheet)

Dim rng As Range
Dim v As Variant

Set rng = ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp))
Dim c As Range

On Error Resume Next
For Each c In rng
    If c.value <> "" Then
        v = Split(c.value, " ")
        c.Offset(, 1).value = v(UBound(v))
    Else
        c.Offset(, 1).value = "No Record"
    End If

Next

END SUB

Thanks
mg

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mohadin]

May be

 v = ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp)).Resize(, 2)
    For i = 1 To UBound(a)
        If v(i, 1) <> "" Then
            v(i, 2) = Split(v(i, 1), " ")(UBound(Split(v(i, 1), " ")))
        Else
            v(i, 2) = "No Record"
        End If
    Next
    ws.Range("A2").Resize(UBound(v, 1), UBound(v, 2)) = v

 

[alansidman]

...and one more method. Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Name.1", "Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Last Name", each if[Name.2]= null then [Name.1] else [Name.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name.2"})
in
    #"Removed Columns"

Load to PQ editor. Split column with space delimiter --right most only.
Add column that if new column is null then take value from first column else split column
Remove first split column

 

[Mallesh23]

Hi Mohadin and AlanSidman,

Thank you both for your help ! and sharing multiple option.


Thanks
mg