Split data into 2 columns in SharePoint using PowerShell and Power Automate

In this blog post, we are going to use SharePoint Online, PnP PowerShell, and also Power Automate (aka Microsoft Flow) so you really needed to know that we were about to built in the title 😅

## The idea

The idea here is to split the data from one column into 2 other separate columns in SharePoint Online. An easy example would be a column being called “Full Name“. We’d want to split this data in a column named “First Name“, and another one called “Last Name“.

Script

Let’s have a look at the script. As usual, we connect to SharePoint Online using the Connect-PnPOnline cmdlet. Then, we target the list containing our column(s) —> called “Clients” in our example.

The next part is the meat and potatoes 😉 Let me explain first what we do, and break things down:

1. We create an array to store the results
2. We fetch the data from FullName, FirstName, and LastName columns using the Get-PnPListItem cmdlet, and store them into a variable called $allItems
3. We create a foreach loop where we…
   • Split the FullName value when there’s a space — hence .Split(" ")
   • Store the 1st object of the Full Name (i.e.: John) into a variable called $firstNameSplit
   • Store the 2nd object of the Full Name (i.e. Smith) into a variable called $lastNameSplit
4. We create our custom object
5. We use the Set-PnPListItem and set the values for our First Name & Last Name columns

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

#Connect to SPO
Connect-PnPOnline -Url https://<TENANT-NAME>.sharepoint.com/sites/<SITE>

#Store my list into a variable
$myList = Get-PnPList -Identity "Clients"

#Get all items from the list + Store the results
$results = @()

$allItems = Get-PnPListItem -List $myList -Fields "FullName", "FirstName", "LastName"

foreach ($item in $allItems) {
    $splitFullName = $item["FullName"].Split(" ")
    $FirstNameSplit = $splitFullName[0]
    $LastNameSplit = $splitFullName[1]
    
    $results += [pscustomobject][ordered]@{
        FullName  = $item["FullName"]
        FirstName = $item["FirstName"]
        LastName  = $item["LastName"]
    }

    #Modify each current item in the list
    Set-PnPListItem -List "Clients" -Identity $item -Values @{"FirstName" = $FirstNameSplit; "LastName" = $LastNameSplit }
}
$results

The Flow

Go into Power Automate (aka Microsoft Flow), and start with an Automated flow.

1. The trigger will be in the SharePoint connector When an item is created. Enter your site URL, as well as your list (i.e.: Clients in our case)
2. Get the items (SharePoint action)
3. Add an Apply to each action with value from the previous step, and in this Apply to each -—> let’s Update the item For the First Name & Last Name column, we’ll use a function as follow (similar to the PowerShell script):

First Name —> split(triggerBody()?['FullName'],' ')[0] Last Name —> split(triggerBody()?['FullName'],' ')[1]

Thanks for reading!