Text manipulation macros The text manipulation macro describes in detail how to use:

• LEFT text function: Here the LEFT function is used to extract the first name from the full name in conjunction with the InStr function
• MID string function: The MID string function is used to extract the last name from the full name using a variable called MyPos
• InStr string function: This string function plays a pivotal role in the macro because it is used to locate the blank space in the full name string which finally provides the solution.
• Have a look at the macro code in the Excel training video to see text functions in action!
• You can also take a look at the training video which shows how to handle a similar problem using text functions.

 

Macro Code (slightly revised):

Sub SeperateNames()

‘define the starting row

x=2

‘start the loop and continue till you encounter a blank cell in column A and row 2

Do While Cells(x,1)<> “”

‘define a variable myStr and set its data type

Dim myStr As String

‘initialize the string

myStr=”Good Day!”

myStr=Cells(x,1)

‘check the position of the blank

MyPos=InStr(myStr, ” “)

‘Extact characters from left of the string upto the blank space and then minus 1 for the blank space Cells(x,2)=Left(myStr, MyPos-1)

‘extract characters from name starting from (blank+1) to avoid getting blank also Cells(x,3)=Mid(myStr, (MyPos+1))

x=x+1

Loop

End Sub

Further reading: String Manipulation