Free Microsoft Excel Training Videos

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


Reference

Text manipulation macros