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