Trending March 2024 # How To Switch First And Last Name In Excel With Comma? # Suggested April 2024 # Top 5 Popular

You are reading the article How To Switch First And Last Name In Excel With Comma? updated in March 2024 on the website Bellydancehcm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 How To Switch First And Last Name In Excel With Comma?

When working with name data sets in Excel, you may want the names to be in a specific format.

Usually, the names are either in the ‘First Name Last Name’ format (such as Joe Davis or Sumit Bansal).

In some specific situations, you may want the names data to be formatted as Last Name, First Name (i.e., last name followed by a comma and then the first name)

In this tutorial, I will show you how to switch the first and the last name in Excel with a comma in between (when you have your data in the First Name Last Name format)

One of the easiest and fastest ways to switch the first and the last name with a comma in between is by using the Flash Fill feature in Excel.

Flash Fill is an amazing tool that works by identifying patterns in your data set (where you need to enter one or two expected result entries for it to identify the pattern).

Let me show you how it works.

Below I have a data set where I have the names in column A, and I want to change this data set by switching the first and the last name and adding a comma in between the names.

Here are the steps to do this using Flash Fill:

In cell B2, enter the expected result (which would be Miller, Flora)

    Press the Enter key

        That’s it!

        Let me quickly explain how this works.

        When I entered the expected result in cell B2 and then used Flash Fill, it used the value in cell B2 to identify the pattern and replicated it for all the cells in the column.

        So it was able to correctly identify that I wanted to change the names data set by switching the first and the last name and adding a comma in between.

        Caution: Since Flash Fill works by identifying the pattern in your data set, it is important that you have a consistent data set. Also, cross-check the result from Flash Fill to ensure that it has picked up the correct pattern.

        Note – The result that you get from Flash Fill is static. So if your original data changes, the resulting data will not automatically update, and you will have to repeat the process.

        Let me now show you how you can switch the first and the last name by using a simple formula.

        While there is no inbuilt function to do this, it can quickly be done by using a combination of text functions.

        Below I have the names data set where I want to switch the first and the last name and add a comma in between.

        Here is the formula that will do this:

        =CONCAT(RIGHT(A2,LEN(A2)-FIND(" ",A2)),", ",LEFT(A2,FIND(" ",A2)-1))

        Enter this formula in cell B2 and copy it for all the other cells in the column.

        Let me explain how this formula works.

        In the formula, I have used the LEFT and the RIGHT function to extract the first name and the last name, respectively.

        This is made possible by identifying the position of the space character (using the FIND function).

        Using the position of the space character, I extracted all the characters on the left, which would be the first name, using the LEFT function.

        And similarly, I extracted all the characters to the right of the space character using the RIGHT function (which gave me the last name).

        And then, I combined the last name and the first name by using the CONCAT function. Also, since I wanted the last name and the first name to have a comma in between, I added a comma followed by a space character as the second argument in the CONCAT function.

        You can also use the below formula, which uses the & operator instead of the CONCAT function:

        =RIGHT(A2,LEN(A2)-FIND(" ",A2))&", "&LEFT(A2,FIND(" ",A2)-1)

        Beware of the Extra Spaces: For this formula to work correctly, you have to make sure that there is only one space character between the first name and the last name. In case there is a possibility that there could be multiple spaces in between the names or there could be a leading or trailing space, then it would be best to use TRIM(A2) instead of A2 in the above formulas.

        Also, note that since we are using a formula to switch the names, in case you change the original data set, the resulting data will automatically update to give you the correct result.

        You can also use the Text to Columns feature to split the first name and last name into separate columns and then combine them in the order you want with the delimiter you want.

        Let me show you how this works.

        Below I have the names data set where I want to switch the position of the first name and the last name and add a comma in between.

        Here are the steps to split the first name and the last name into separate columns using the Text to Columns feature:

        Select the names data set in column A

                  In Step 3 of 3, specify the destination cell (where you want to get the results). In this example, I would select cell B2 as the destination cell. If you do not select the destination cell, your existing name data will be overwritten by the result.

                    The above steps would split the names data set into two separate columns – one with first names and the other with last names.

                    Once you have the first and the last name in separate columns, you can use the formula below to combine these names in the desired order (i.e., the last name followed by a comma and then the first name)

                    =CONCAT(C2,", ",B2)

                    Enter the formula in cell D2 and copy it for all the cells in the column to get all the names.

                    The above formula uses the CONCAT function to combine three elements:

                    The last name, which is in cell B2

                    A comma followed by a space character (which needs to be in double quotes)

                    The first name, which is in cell B2

                    Power Query is another way to switch the first and the last name and add the desired delimiter in between.

                    The Power Query method should only be used if you are already using It to transform your data and, as a part of it, also wants to change the names data set. Or if you have to do this quite often so, instead of doing it using the Flash Fill method or the formula method every time, you can use Power Query to do it once and then use the same query over and over again with different data sets.

                    But if this is a one-time activity where you want to reverse the position of the first name and the last name, it is best to use the other methods such as Flash Fill or formula (covered earlier in this tutorial)

                    Below I have the names data set where I want to switch the first and the last name and separate them by a comma and a space character.

                    Below are the steps to do this using Power Query:

                    Select the range that contains the names

                                Enter a comma followed by a space character (, ) in the field below the Custom option

                                  Enter the name for the new merged column. I will go with the name ‘Full Names’

                                      The above steps would insert a new worksheet in your workbook and give you the resulting data in an Excel Table.

                                      I’m sure you’re thinking about how long this method is and whether this is worth it or not.

                                      As I mentioned at the beginning of this section, if you only want to do this once or twice, it’s best not to use Power Query.

                                      But if this is something you need to do quite often, then you can follow all the above steps to create the query once, and the next time you have a new data set, you can connect your query to that table and get the result instantly.

                                      Or if you make any changes to the already existing names data set, you don’t need to repeat the process.

                                      Now let me show you how to reverse the first and the last name using a simple VBA code.

                                      Below is the VBA macro code that would switch the first and the last name position and add a comma in between

                                      Sub ReverseNames() Dim FindSpace As Long For Each Cell In Selection FindSpace = InStr(Cell.Value, ” “) Cell.Value = Trim$(Mid$(Cell.Value, FindSpace + 1)) _ & “, ” & Left$(Cell.Value, FindSpace – 1) End If Next Cell End Sub

                                      The above VBA code uses a For Each Next loop to go through each cell in the column and then uses the INSTR function to find out the position of the space character in the cell.

                                      Once it knows the position of the space character, it uses the MID function and the LEFT function to extract the last name and the first name, respectively, and combine them using an & operator.

                                      Here are the steps to use this code:

                                      Select the names dataset (excluding the header)

                                            Copy and paste the above VBA code into the module code window

                                              The above steps would instantly change your original data set and give you the result as shown below:

                                              While there is some initial setup required to use the VBA method, it is helpful in case you want to change the names data set in multiple locations in your existing workbook.

                                              You can also save this macro code in the Personal Macro Workbook so that it can be used in any workbook on your system.

                                              This would be quite a time saver as you can open any Excel workbook where you have the names data and then run this code to switch the first and the last name (separated by a comma).

                                              So these are the five methods you can use to reverse the first name and the last name and add a comma in between the names.

                                              If you need to do it once in a while, you can use the Flash Fill method, the formula method, or the Text to Columns method.

                                              And if you need to do this quite often, consider using the Power Query or the VBA method.

                                              Other Excel articles you may also like:

                                              You're reading How To Switch First And Last Name In Excel With Comma?

                                              How To Use Boolean In Excel Vba With Excel Template?

                                              VBA Boolean Operation

                                              In Excel when we compare two cell contents or numbers by applying equal sign between them, we get output in TRUE or FALSE format. Which means values which we are comparing may be equal or may not be equal. In a similar manner, we have Boolean in VBA Excel. Boolean also gives the output in the form of TRUE or FALSE. Boolean is a form of data type which can only contain either TRUE or FALSE. When we give some input to Boolean and if the test becomes TRUE then we get an answer as TRUE or we get FALSE.

                                              How to Use Boolean in VBA Excel?

                                              Let’s see the examples of Boolean in Excel VBA.

                                              Watch our Demo Courses and Videos

                                              Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

                                              You can download this VBA Boolean Excel Template here – VBA Boolean Excel Template

                                              Example #1 – VBA Boolean

                                              Let’s see a very simple example where we will see how to apply Boolean while comparing some.

                                              Step 2: Now in the opened module, write the sub category of VBA Boolean. We can choose to write any name of subprocedure here.

                                              Code:

                                              Sub

                                              VBABoolean1()

                                              End Sub

                                              Step 3: Now define a Dim with any name, let’ say an A and assign the variable A as Boolean as shown below.

                                              Sub

                                              VBABoolean1()

                                              Dim

                                              A

                                              As Boolean

                                              End Sub

                                              Step 4: Let’s consider two numbers, 1 and 2. And now we will test with the help of Boolean whether 2 is greater than 1 or not. So in the next line, write a mathematical expression of 1<2 under defined variable A.

                                              Code:

                                              Sub

                                              VBABoolean1()

                                              Dim

                                              A

                                              As Boolean

                                              A = 1 < 2

                                              End Sub

                                              Step 5: Now assign a message box to variable A to see what outcome will appear when we run the code.

                                              Code:

                                              Sub

                                              VBABoolean1()

                                              Dim

                                              A

                                              As Boolean

                                              A = 1 < 2 MsgBox A

                                              End Sub

                                              Step 7: If we change the sign as 1 is greater than 2 as shown below. What would we get?

                                              Code:

                                              Sub

                                              VBABoolean1()

                                              Dim

                                              A

                                              As Boolean

                                              MsgBox A

                                              End Sub

                                              Step 8: To test this, again run the code. We will see, Boolean has given FALSE as 1 cannot be greater than 2.

                                              Example #2 – VBA Boolean

                                              In this example, we will test if Boolean works for text or not. To apply this, we need a module.

                                              Step 1: Open a new Module and give it a subcategory in the name of VBA Boolean or any name as per your choice.

                                              Sub

                                              VBABoolean2()

                                              End Sub

                                              Step 2: Define a variable A and assign a Boolean function to it.

                                              Code:

                                              Sub

                                              VBABoolean2()

                                              Dim

                                              A

                                              As Boolean

                                              End Sub

                                              Step 3: Now assign a text to defined variable A. Let’s say that text is VBA Boolean. And it should be under inverted commas.

                                              Code:

                                              Sub

                                              VBABoolean2()

                                              Dim

                                              A

                                              As Boolean

                                              A = "VBA  Boolean"

                                              End Sub

                                              Step 4: At last, give that variable A in a message box to see the output as shown below.

                                              Code:

                                              Sub

                                              VBABoolean2()

                                              Dim

                                              A

                                              As Boolean

                                              A = "VBA Boolean" MsgBox A

                                              End Sub

                                              Step 5: Once done, run the code. We will get an error message as “Run-time error 12 – Type Mismatch” which means that Boolean doesn’t support input as Text.

                                              Example #3 – VBA Boolean

                                              In this example, we will see, if Boolean works for a number without any comparison.

                                              Step 1: Open a new module and give it a subcategory of VBA Boolean as shown below.

                                              Code:

                                              Sub

                                              VBABoolean3()

                                              End Sub

                                              Step 2: Now define a Dim A variable as Boolean as shown below.

                                              Code:

                                              Sub

                                              VBABoolean3()

                                              Dim

                                              A

                                              As Boolean

                                              End Sub

                                              Step 3: As discussed above, we will give the variable A a number. Let’s consider that number is 10.

                                              Code:

                                              Sub

                                              VBABoolean3()

                                              Dim

                                              A

                                              As Boolean

                                              A = 10

                                              End Sub

                                              Step 4: After that, select the function msgbox and assign it to variable A. This will help us print the value with the help of Boolean.

                                              Code:

                                              Sub

                                              VBABoolean3()

                                              Dim

                                              A

                                              As Boolean

                                              A = 10 MsgBox A

                                              End Sub

                                              Step 5: Now run the code. We will get the message with the message as TRUE.

                                              Step 6: Now let’s change that value to 0 from 10.

                                              Code:

                                              Sub

                                              VBABoolean3()

                                              Dim

                                              A

                                              As Boolean

                                              A = 0 MsgBox A

                                              End Sub

                                              Example #4 – VBA Boolean

                                              Step 1: Now, open a new module and write the subcategory of VBA Boolean as shown below.

                                              Code:

                                              Sub

                                              VBABoolean4()

                                              End Sub

                                              Step 2: Now define 2 variable with any name as per your choice. Here, we have selected A and B as Integer. Which means both will store numeric values.

                                              Code:

                                              Sub

                                              VBABoolean4()

                                              Dim

                                              A

                                              As Integer

                                              Dim

                                              B

                                              As Integer

                                              End Sub

                                              Step 3: Now assign any values to variable A and B. Here we have chosen number 1 and 2 for variable A and B as shown below.

                                              Code:

                                              Sub

                                              VBABoolean4()

                                              Dim

                                              A

                                              As Integer

                                              Dim

                                              B

                                              As Integer

                                              A = 1 B = 2

                                              End Sub

                                              Step 4: As stated above, we will use the If-Else loop. Now open the If-End If loop where we will write the criteria.

                                              Code:

                                              Sub

                                              VBABoolean4()

                                              Dim

                                              A

                                              As Integer

                                              Dim

                                              B

                                              As Integer

                                              A = 1 B = 2 If

                                              End If

                                              End Sub

                                              Step 5: Now write the code, If A is less than equal to B then show us the message as TRUE, else show us the message as FALSE.

                                              Code:

                                              Sub

                                              VBABoolean4()

                                              Dim

                                              A

                                              As Integer

                                              Dim

                                              B

                                              As Integer

                                              A = 1 B = 2

                                              If

                                              A <= B

                                              Then

                                              MsgBox

                                              True

                                              Else

                                              MsgBox

                                              False

                                              End If

                                              End Sub

                                              Step 6: Now compile the above code step-by-step and then run if no error found. We will see, the message box has the message as TRUE which means value stored in variable A (which is 1) is less than the value stored in variable B (which is 2).

                                              Pros of VBA Boolean

                                              It is quite useful when we are want to implement the process flow following TRUE and FALSE for each iteration.

                                              Conditional comparison with the help of any kind of loop can easily be implemented.

                                              Cons of VBA Boolean

                                              Only numbers can be used in Boolean. It will show the error if used for text as seen in example-2.

                                              Things to Remember

                                              Using Boolean with any loop will give users a better output. And comparison can be done in various ways.

                                              Boolean is a kind of cell comparison formula used in excel, but it only compares the numerical or mathematical values.

                                              Always save the file in macro enable format to retain the written code to be used in the future.

                                              Recommended Articles

                                              This is a guide to VBA Boolean. Here we discuss how to use Boolean in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

                                              How To Create Arraylist In Excel Vba With Excel Template?

                                              Excel VBA ArrayList

                                              Data structures are used to store a series of data in programming languages. It binds to the memory rather than address. An ArrayList is one of the data structures in excel. Comparing to normal arrays in excel ArrayList is dynamic. Therefore, no initial declaration of size is needed. ArrayList is not a part of VBA it is associated with an external library which can be used with VBA.

                                              ArrayList can be defined as a list of a nearby memory location. Where the values are retrieved using the index numbers. The list starts from an index number ‘0’, the first element will be inserted into the ‘0’ index and rest is followed by 1, 2, 3, etc. ArrayList offers plenty of built-in operations, sorting, adding, removing, reversing, etc. are some among them.

                                              Watch our Demo Courses and Videos

                                              Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

                                              Adding the Library

                                              To use the ArrayList into the VBA it needs to include the library ‘mscorlib.dll’ which comes with .NET framework.

                                              It will lead you to a window with a list of different libraries which supports in VBA and Excel. Scroll down to find the ‘dll’. Tick mark to confirm the selection then press ‘OK’ button.

                                              Now the library is included in your VBA code and it will support different methods associated with an ArrayList.

                                              How to Create VBA ArrayList in Excel?

                                              You can download this VBA ArrayList Excel Template here – VBA ArrayList Excel Template

                                              Excel VBA ArrayList – Example #1

                                              How to Add Values to the ArrayList using VBA?

                                              ArrayList act as a list where we can add values. This will automatically store in the different portions starting from 0,1, 2, etc. The values can add or insert to the ArrayList using the add method.

                                              In this example, you will learn how to add a list of values into an ArrayList. Follow the below steps to add ArrayList using VBA Code in excel.

                                              Step 1: To add a list of values to an ArrayList create a function arraylist1.

                                              Code:

                                              Private Sub

                                              arraylist1()

                                              End Sub

                                              Step 2: Now we want to include the ArrayList into the function as an object where a list is declared as an ArrayList.

                                              Code:

                                              Private Sub

                                              arraylist1()

                                              Dim

                                              alist

                                              As ArrayList

                                              End Sub

                                              Step 3: Since this is an object to use it, you have to create an instance of the ArrayList. Set a new instance for this object.

                                              Code:

                                              Private Sub

                                              arraylist1()

                                              Dim

                                              alist

                                              As ArrayList

                                              Set

                                              alist =

                                              New

                                              ArrayList

                                              End Sub

                                              Step 4: Now using the ‘Add’ property of an ArrayList adds the values to the ArrayList. Where the list is added into the index values in an order 0,1,2,3 etc.

                                              Code:

                                              Private Sub

                                              arraylist1()

                                              Dim

                                              alist

                                              As ArrayList

                                              Set

                                              alist =

                                              New

                                              ArrayList alist.Add "192"

                                              'index(0)

                                              alist.Add "168"

                                              'index(1)

                                              alist.Add "1"

                                              'index(2)

                                              alist.Add "240"

                                              'index(3)

                                              End Sub

                                              Step 5: To check whether the values got added into the list, let’s print the array values using a message box. To print the values each index is printed since the values are stored in these partitions.

                                              Code:

                                              Private Sub

                                              arraylist1()

                                              Dim

                                              alist

                                              As ArrayList

                                              Set

                                              alist =

                                              New

                                              ArrayList alist.Add "192"

                                              'index(0)

                                              alist.Add "168"

                                              'index(1)

                                              alist.Add "1"

                                              'index(2)

                                              alist.Add "240"

                                              'index(3)

                                              MsgBox ("\" & alist(0) & "." & alist(1) & "." & alist(2) & "." & alist(3))

                                              End Sub

                                              Step 6: Press F5 or run button to run the program and the values will be printed as below. Here an IP address is stored in the ArrayList and while printing the values extra notations are concatenated to form the IP address in a proper format.

                                              Automation error in VBA

                                              It is a common error happens while running an ArrayList. An automation error may encounter ‘Run-time Error ‘-2146232576 (80131700) Automation Error’

                                              Excel VBA ArrayList – Example #2

                                              Sorting ArrayList Using VBA Code 

                                              ArrayList supports different functions like sorting, reversing, etc. this help to sort the values inserted into an ArrayList. Once you add a list into the ArrayList it is possible to reverse the inserted list.

                                              Follow the below steps to sort the ArrayList using VBA Code:

                                              Step 1: Create a function called arraysort1 to perform the sorting within the inserted values into an ArrayList.

                                              Code:

                                              Sub

                                              arraysort1()

                                              End Sub

                                              Step 2: Declare an object ‘arraysort’ of the ArrayList. Use this object to add and sort the values within the ArrayList.

                                              Code:

                                              Sub

                                              arraysort1()

                                              Dim

                                              arraysort

                                              As ArrayList

                                              End Sub

                                              Step 3: Similar to the first example need to create a new instance of the declared object. Set this instance as a new ArrayList.

                                              Code:

                                              Sub

                                              arraysort1()

                                              Dim

                                              arraysort

                                              As ArrayList

                                              Set

                                              arraysort =

                                              New

                                              ArrayList

                                              End Sub

                                              Step 4: Now using the ‘Add’ method insert the elements to the ArrayList. Which is not possessing any order on values. Randomly inserted some values into the list.

                                              Code:

                                              Sub

                                              arraysort1()

                                              Dim

                                              arraysort

                                              As ArrayList

                                              Set

                                              arraysort =

                                              New

                                              ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45"

                                              End Sub

                                              Code:

                                              Sub

                                              arraysort1()

                                              Dim

                                              arraysort

                                              As ArrayList

                                              Set

                                              arraysort =

                                              New

                                              ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

                                              End Sub

                                              Step 6: Press F5 on the keyboard or run button on the code window to run the program to print the ArrayList. The ArrayList is printed in the same order as it is inserted since we use the index numbers in its correct order.

                                              Step 7: Now to this list apply the sort property of the ArrayList. Use the sort method to sort the inserted list. The sort property will sort the list of values in ascending order by default.

                                              Code:

                                              Sub

                                              arraysort1()

                                              Dim

                                              arraysort

                                              As ArrayList

                                              Set

                                              arraysort =

                                              New

                                              ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" arraysort.Sort MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

                                              End Sub

                                              Step 8: Hit F5 or Run button under VBE to run this code, Where the values are sorted and printed in order from smallest value to largest.

                                              Excel VBA ArrayList – Example #3

                                              Reversing the ArrayList using VBA Code

                                              When you want to reverse the order of inserted values in an ArrayList reverse method is available. This will reverse the order of the list from its current order. Now we have already sorted the ArrayList in the previous example, which is in ascending order.

                                              Let’s try to reverse the sorted array to make it descending order. Use the reverse method of ArrayList to do this.

                                              Code:

                                              Sub

                                              arraysort2()

                                              Dim

                                              arraysort

                                              As ArrayList

                                              Set

                                              arraysort =

                                              New

                                              ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" arraysort.Sort arraysort.Reverse MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

                                              End Sub

                                              After applying the reverse method, the ArrayList will become in descending order and use the message box to print the reversed array. The sorted list is changed from large value to small value.

                                              Things to Remember

                                              ArrayList is dynamic in nature; it does not require re-initialization.

                                              Different built-in methods are associated with ArrayList.

                                              Compared to the array, ArrayList is easy to use in Excel VBA.

                                              The supporting .NET libraries should be included in the VBA to work with ArrayList.

                                              ArrayList is a continuing memory location which identified using index values.

                                              Recommended Articles

                                              This is a guide to VBA ArrayList. Here we discuss how to create ArrayList in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

                                              How To Group Processes With Their Name In Powershell?

                                              —-                                                           —– svchost                                                           91 chrome                                                            34 RuntimeBroker                                                     11 conhost                                                            6 Code                                                               6 WmiPrvSE                                                           6 dllhost                                                            4 RAVBg64                                                            4 powershell                                                         3 csrss                                                              2 fontdrvhost                                                        2 AcroRd32                                                           2 taskhostw                                                          2 SkypeBridge                                                        1 smartscreen                                                        1 smss                                                               1 sihost                                                             1 SkypeApp                                                           1 SkypeBackgroundHost                                                1 sppsvc                                                             1 StartMenuExperienceHost                                            1

                                              Formula Challenge #4: Sort A Column By Last Name

                                              This Formula Challenge originally appeared as Tip #108 of my weekly Google Sheets Tips newsletter, on 29 June 2023. Sign up so you don’t miss future Formula Challenges!

                                              Sign up here so you don’t miss out on future Formula Challenges:

                                              Find all the Formula Challenges archived here.

                                              The Challenge: Sort A Column By Last Name

                                              Start with a list of any ten two-word names in column A, like so:

                                              Your challenge is to create a single formula in cell B1 (shown in yellow below) that sorts this list alphabetically by the last name, like this:

                                              Assumptions:

                                              The names to sort are in the range A1:A10

                                              Each name consists of a first name and last name only, with a single space between them

                                              None of the names have prefixes or suffixes

                                              The formula in cell B1 should output the full names in range B1:B10

                                              Solutions To Sort A Column By Last Name

                                              I received over 90 replies to this formula challenge with at least 10 different methods for solving it. This was super interesting to see!

                                              Congratulations to everyone who took part.

                                              I learnt so much from the different replies, many of which proffered shorter and more elegant solutions than my own original formula.

                                              Here I present the best 4 solutions, chosen for their simplicity and elegance.

                                              There’s a lot to learn by looking through them.

                                              1. SORT/INDEX/SPLIT method

                                              Solution:

                                              Use this formula in cell B1 to sort the data alphabetically by the last name:

                                              =SORT(

                                              A1:A10

                                              ,INDEX(SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              ),,

                                              2

                                              ),

                                              1

                                              )

                                              This is a beautiful solution.

                                              It’s deceptively simple, as you’ll see below when we break it down. And yet, only a handful of people submitted this solution.

                                              How does this formula work?

                                              Let’s use the onion method to peel back the layers and build this formula in steps from the innermost layer.

                                              Step 1:

                                              Enter this formula as a starting point:

                                              =SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              )

                                              The SPLIT function takes the range A1:A10 as an input and splits the names on the space character. That works in this example because of our rigid assumptions that all names are two-word names.

                                              The output is a single row with the name split across two cells. (Adding an ArrayFormula would give an output of all ten names, but it’s not required when we plug this split into the INDEX function.)

                                              Step 2:

                                              Wrap the formula from step 1 in an INDEX function:

                                              =INDEX(SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              ),,

                                              2

                                              )

                                              Leave the row argument (the second argument) in the function blank and choose column 2 in the column argument (the third one). This formula outputs the list of second names, still in the original order, however.

                                              Step 3:

                                              The SORT function takes three arguments: the range to sort, the column to sort on, and whether to sort ascending or descending.

                                              So A1:A10, the range containing the full names, is the range we want to sort.

                                              Then the column of last names created in step 2 is the column we want to sort on. This is our second argument.

                                              Then the third argument is a TRUE or 1 to indicate we want to sort the names ascending.

                                              So plugging these three arguments into the SORT function gives the following formula:

                                              =SORT(

                                              A1:A10

                                              ,INDEX(SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              ),,

                                              2

                                              ),

                                              1

                                              )

                                              This formula can be modified to include the whole of column A if there are more than 10 names. Simply change the range from A1:A10 to A1:A as follows:

                                              =SORT(

                                              A1:A

                                              ,INDEX(SPLIT(

                                              A1:A

                                              ,

                                              " "

                                              ),,

                                              2

                                              ),

                                              1

                                              )

                                              2. QUERY method

                                              Solution:

                                              Use this formula in cell B1 to sort the data alphabetically by the last name:

                                              =ArrayFormula(QUERY({

                                              A1:A10

                                              ,SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              )},

                                              "select Col1 order by Col3"

                                              ))

                                              Step 1:

                                              Step 1 is the same as the step 1 in solution 1 above, and uses the SPLIT to separate the names into first and last names in separate columns:

                                              =SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              )

                                              Step 2:

                                              Step 2 combines these two split columns with the original column of names using array literals to construct an array of full name, first name and last name.

                                              =ArrayFormula({

                                              A1:A10

                                              ,SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              )})

                                              The ArrayFormula wrapper is necessary to output all 10 values in this example.

                                              Step 3:

                                              The array created by step 2 is used as the input data range in the QUERY function in step 3:

                                              =ArrayFormula(QUERY({

                                              A1:A10

                                              ,SPLIT(

                                              A1:A10

                                              ,

                                              " "

                                              )},

                                              "select Col1 order by Col3"

                                              ))

                                              Since the input range of the QUERY function is constructed with the curly brace notation {…} we are required to use the Col1, Col2, etc. notation to access the columns. The query clause is pretty simple. Select only column 1 of the array (the full name) but sort by column 3 (the last name).

                                              Note, the ArrayFormula can be brought outside the QUERY to be the outer wrapper.

                                              This formula can be extended easily to the whole of column A by changing the range to A1:A and adding a WHERE clause to filter out the blank rows:

                                              =ArrayFormula(QUERY({

                                              A1:A

                                              ,SPLIT(

                                              A1:A

                                              ,

                                              " "

                                              )},

                                              "select Col1 where Col1 is not null order by Col3"

                                              ))

                                              3. REGEX method I

                                              Solution:

                                              Use this formula in cell B1 to sort the data alphabetically by the last name:

                                              =INDEX(SORT(REGEXEXTRACT(

                                              A1:A10

                                              ,

                                              "((.*)( .*))"

                                              ),

                                              3

                                              ,

                                              1

                                              ),,

                                              1

                                              )

                                              If the first solution was the shortest and simplest, then this is perhaps the most elegant.

                                              It only references the data range A1:A10 once, whereas all the other solutions presented here reference the data range twice.

                                              Step 1:

                                              Use the Google Sheets REGEXEXTRACT function to split the data into three columns of full name, first name and last name:

                                              =REGEXEXTRACT(

                                              A1:A10

                                              ,

                                              "((.*)( .*))"

                                              )

                                              This formula uses numbered capturing groups to capture the data, denoted by (.*) and ( .*) with the second having a space. The .* simply says match zero or more of any character.

                                              In other words, the (.*)( .*) construction separates into first and last names. Adding another matching group with the additional parentheses ((.*)( .*)) also matches the full name.

                                              These three columns are passed into the SORT function in STEP 2:

                                              Step 2:

                                              =SORT(REGEXEXTRACT(

                                              A1:A10

                                              ,

                                              "((.*)( .*))"

                                              ),

                                              3

                                              ,

                                              1

                                              )

                                              The data is sorted on column 3 containing the last name. The 1 represents a TRUE value which sorts the data ascending from A-Z.

                                              Step 3:

                                              In the final step, we use the INDEX function to return only the first column, which has the full name.

                                              =INDEX(SORT(REGEXEXTRACT(

                                              A1:A10

                                              ,

                                              "((.*)( .*))"

                                              ),

                                              3

                                              ,

                                              1

                                              ),,

                                              1

                                              )

                                              4. REGEX method II

                                              Solution:

                                              Use this formula in cell B1 to sort the data alphabetically by the last name:

                                              =SORT(

                                              A1:A10

                                              ,REGEXEXTRACT(

                                              A1:A10

                                              ,

                                              "(?: )(w*)"

                                              ),

                                              1

                                              )

                                              This is the same method as the first solution, but uses REGEXEXTRACT instead of INDEX/SPLIT. So it’s one less function, but the REGEXEXTRACT function is much harder to understand than the INDEX/SPLIT combination for those unfamiliar with the black magic of regular expressions.

                                              Step 1:

                                              Use the REGEXEXTRACT function to extract the last names from the data:

                                              =REGEXEXTRACT(

                                              A1:A10

                                              ,

                                              "(?: )(w*)"

                                              )

                                              The (?: ) is a non-capturing group matching the space. In other words the REGEXEXTRACT matches the space but doesn’t extract it.

                                              The w character matches word characters (≡ [0-9A-Za-z_]) and the * means match zero or more word characters but prefer more.

                                              The parentheses ( ) around the w* make it a captured group so it’s extracted.

                                              See, regular expressions are as clear as mud.

                                              Ok, so what’s happening is that it matches on the space, but doesn’t return it. Then it matches on the word group that follows the space and returns that. Hence we get an output range of last names only.

                                              Step 2:

                                              The final step is to sort range A1:A10 using the last names extracted in step 2, and sort them ascending. This step is identical to the implementation in solution 1.

                                              =SORT(

                                              A1:A10

                                              ,REGEXEXTRACT(

                                              A1:A10

                                              ,

                                              "(?: )(w*)"

                                              ),

                                              1

                                              )

                                              This formula can be modified to include the whole of column A if there are more than 10 names. Simply change the range from A1:A10 to A1:A as follows:

                                              =SORT(

                                              A1:A

                                              ,REGEXEXTRACT(

                                              A1:A

                                              ,

                                              "(?: )(w*)"

                                              ),

                                              1

                                              )

                                              There we go!

                                              Four brilliant solutions to an interesting formula challenge.

                                              And don’t forget to sign up to my Google Sheets Tips newsletter so you don’t miss future formula challenges!

                                              How To Use Goal Seek In Excel (With Real

                                              If you know the outcome of a formula but don’t know the input value that’ll generate the outcome, Microsoft Excel got you covered. Simply learn how to use Goal Seek in Excel and perform simulations of formulas and large numerical data without changing the actual data.

                                              Whether it’s your personal finances, savings investments, or business project, informed decision-making is the key to success. For informed decision making you need simulated data from the simulation of databases based on a formula and your objective. Excel Goal Seek is the tool for such data-based simulation.

                                              If you’re new to Excel or have some experience using it but are not an expert, read on to learn the basics of Goal Seek on Excel and how to use Goal Seek function in Excel.

                                              What Is Goal Seek in Excel?

                                              Goal Seek is the built-in What-If Analysis tool of Excel. It shows how the final value in a formula impacts the input value. In easy words, it tells you the input value for a formula to get a desired outcome from the same formula.

                                              Financial investors, finance managers, business owners, and management majors abundantly use this Excel What-If Analysis tool to predict the input for a desired outcome from any mathematical model.

                                              You can also call the Goal Seek on Excel an automated simulation system that analyzes your formula linked to any size of datasets and tells you an input value. If you enter the input value in your mathematical model, you’ll get the result you’re looking for.

                                              You can use this tool in almost any scenario but the followings are the major ones:

                                              How many units of products you must sell to achieve a specific revenue with added discounts and agent commissions

                                              At what interest rate you must borrow so you can pay off the loan within a specific period at specific monthly installments

                                              How many work hours needed to complete a project within a predetermined deadline

                                              Where Is Goal Seek in Excel?

                                              The location of the tool is the same in Excel for the Mac app. At the time of writing, the feature is available in the following Excel editions:

                                              Excel for Microsoft 365 (Windows and Mac)

                                              Excel 2023 (Windows and Mac)

                                              Also available for Windows and Mac from Excel 2010 and later

                                              Requirements to Use Goal Seek in Excel

                                              Excel Goal Seek will perform all the calculations in the backend. All you need to do is define the following three parameters from your mathematical model or data table:

                                              Enter a cell reference in the Set cell box. This reference cell must contain a formula. Excel Goal Seek uses this formula to derive the input value.

                                              You need to enter the result or goal you need to achieve in the To value box.

                                              The By changing cell is the cell reference in which you need an input for the mathematical model you’re simulating.

                                              How to Use Goal Seek in Excel

                                              Find below some example scenarios where you can use Goal Seek in Excel to predict an input for a desired output from a mathematical formula. The steps are similar for both Windows 11 and macOS devices.

                                              1. Goal Seek to Predict Exam Score

                                              Let’s say, you need to pace up your study plans in the final semester of college to achieve an 85% average score when you graduate. Here’s how you can apply the Goal Seek formula to simulate the score needed in the final semester if you got scores from the other seven semesters:

                                              Create column headers named Semester and Score on an Excel worksheet.

                                              Type Semester I through Semester VIII under the Semester column.

                                              Enter the scores of all seven-semester exams except for Semester VIII.

                                              Create a Semester Average score by using the following formula:

                                              =AVERAGE(B2:B9)

                                              Adjust cell references as per your own Excel worksheet.

                                              Now, call the Goal Seek function.

                                              The Set cell should be the formula cell where you calculated an average.

                                              The To value cell should be your desired average semester score which is 85%.

                                              Enter the blank cell reference for the Semester VIII exam score in the By changing cell box.

                                              You need to score 98% in the final semester to get to the target graduation score.

                                              2. Predict Loan Amount Using Goal Seek

                                              Suppose you need to borrow capital for 40 years on a fixed interest (8%) with a fixed monthly payoff for your business. Now, you know that you can pay $1,800 per month from the returns of the business. However, you don’t know how much you must borrow at the above interest for the above period. You can effortlessly calculate the amount to be borrowed by following these steps:

                                              Create rows for the following components: Annual Interest Rate, Years to Payoff, Loan Amount, and Monthly Installments.

                                              Enter the respective values for the above components in the respective cells except for the Loan Amount, which you need to calculate using Goal Seek.

                                              Now, replace the Monthly Installments value with the following PMT formula in Excel:

                                              =PMT(B1/12,B2*12,B3,0)

                                              Set cell should refer to the PMT formula cell.

                                              To value should be $1,500.

                                              By changing cell should be the Loan Amount value.

                                              According to the Goal Seek function, the loan amount must be $215,731 if you wish to borrow it for 40 years at 8% annual interest and pay a monthly installment of $1,500.

                                              3. Find Sales Target Using Goal Seek

                                              If you’re the head of sales and marketing or working as a sales executive and need to create a sales target to achieve a specific revenue goal, Goal Seek is the ideal tool. For example, your business is currently selling 1,000 units of products.

                                              The MSRP of the product is $50 and you’re also offering a discount of 10% to the wholesalers. However, you’d like to increase your company’s revenue target from product sales to $75,000 without changing the MSRP and discount rate. Then, how many products you must sell? Let’s find out using Goal Seek below:

                                              Create rows for known components like Units Sold, MSRP, and Discount.

                                              Calculate the revenue using the following formula:

                                              =B2*(B3*(1-B4))

                                              Now, open Goal Seek and set the following as its parameters:

                                              Set cell: $B$5

                                              To value: $75,000

                                              By changing cell: $B$2

                                              Ensure you adjust the cell references according to your own data and Excel worksheet. Press OK and you get the sales target of products which is 1,667 units at the same MSRP and discount rate.

                                              Goal Seek on Excel: FAQs How Do I Use Goal Seek in Excel for Multiple Cells?

                                              Goal Seek in Excel enables you to simulate an input for a mathematical formula to get a predetermined result. Unfortunately, it doesn’t support any variables or multiple cells to be factored in. For multiple cells and variation-based What-If Analysis, you can use the Solver add-in of Excel.

                                              What Is the Shortcut for Goal Seek?

                                              Currently, there is no dedicated shortcut for the Goal Seek function in Excel. You can use the Alt key on Excel and then press some keys on the keyboard to call the Goal Seek tool without using a mouse. The steps are as outlined here:

                                              How Do I Make My Goal Seek More Accurate?

                                              Sometimes, Goal Seek may derive the input value in decimals. If you need a rounded figure automatically, you can increase its accuracy. Here’s how you can do it:

                                              Checkmark the Enable iterative calculation feature.

                                              Increase the Maximum Iterations field to 150 or more to let Excel test more feasible solutions.

                                              Also, increase the Maximum Change value from default 0.001 to 0.00001.

                                              Can I Do Goal Seek on Multiple Cells?

                                              You can’t perform a Goal Seek on Excel for multiple cells.

                                              What Are the Advantages of Goal Seek?

                                              Here are the benefits of Goal Seek on Excel:

                                              Goal Seek is the simplest simulation available for What-If Analysis in Excel

                                              It comes out of the box with the Excel desktop app

                                              Goal Seek works in the backend and doesn’t require you to modify the whole database

                                              Useful for financial modeling

                                              What Are the Limitations of Goal Seek in Excel?

                                              Goal Seek on Excel has the following drawbacks:

                                              Goal Seek won’t work if there are circular references

                                              You can’t input any variables when performing Goal Seek simulations

                                              It doesn’t work on multiple cells

                                              Excel Goal Seek isn’t available on Excel Web App or Excel for the Web app

                                              Excel Goal Seek: Final Words

                                              By reading the above article on Excel Goal Seek, you should have developed a basic to medium-level understanding of how to do What-If Analysis in Excel. Goal Seek on Excel is particularly useful in mathematical tasks where there’s a clear formula, an input value, a few variables linked to the formula, and one objective to be achieved.

                                              Next up, how to compare text in Excel.

                                              Update the detailed information about How To Switch First And Last Name In Excel With Comma? on the Bellydancehcm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!