cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Digital
Impactful Individual
Impactful Individual

Substrings from not very well structured text to populate SharePoint columns

I need to transfer data from one SharePoint list to another. The orginal list has a column that contains data in a not very well stuctured text string. I would like to extract values from this string to populate columns in my new list using Power Automate.

 

Looking at this is it possible using string operations/regex to get the data in an array that I could then process to populate SharePoint columns? 

 

To make it even trickier the Transferring/Receiving Manager's are person columns and Expected Send Date and Expected Arrival are of type date.

 

VEHICLE DETAILS
Fleet Number: 123
Registration Number: ABC123
Movement Recommended By: Isaac Asimov
Expected Send Date: 23/07/2021
Expected Arrival: 23/07/2021

----------------------------- -----------------------------
----------------------------- -----------------------------

TRANSFER FROM
Please confirm Transferring Manager: Arthur Clarke

----------------------------- -----------------------------

TRANSFER TO
Please confirm Receiving Manager: Ian Banks

----------------------------- -----------------------------

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

Hi @Digital

 

Get Fleet Number

substring(variables('SPInput'),add(IndexOf(variables('SPInput'),'Fleet Number:'),length('Fleet Number:')),sub(sub(IndexOf(variables('SPInput'),'Registration Number:'),IndexOf(variables('SPInput'),'Fleet Number:')),length('Registration ')))

 

Get Registration Number

substring(variables('SPInput'),add(IndexOf(variables('SPInput'),'Registration Number:'),length('Registration Number:')),sub(sub(IndexOf(variables('SPInput'),'Movement Recommended By:'),IndexOf(variables('SPInput'),'Registration Number:')),length('Movement Recommended')))

 

View solution in original post

5 REPLIES 5
StalinPonnusamy
Community Champion
Community Champion

We can get values unless the structure is not changed. For example, the Fleet number is always next to the Vehicle Details and before the Registration number.

 

Do you think those order changes?

Digital
Impactful Individual
Impactful Individual

@StalinPonnusamy 

 

The order always stays the same it is only the values after the colons that change.

StalinPonnusamy
Community Champion
Community Champion

Hi @Digital

 

Then this is possible. No worries. I will get something for you.

Digital
Impactful Individual
Impactful Individual

Very much appreciated. Thank you @StalinPonnusamy 

StalinPonnusamy
Community Champion
Community Champion

Hi @Digital

 

Get Fleet Number

substring(variables('SPInput'),add(IndexOf(variables('SPInput'),'Fleet Number:'),length('Fleet Number:')),sub(sub(IndexOf(variables('SPInput'),'Registration Number:'),IndexOf(variables('SPInput'),'Fleet Number:')),length('Registration ')))

 

Get Registration Number

substring(variables('SPInput'),add(IndexOf(variables('SPInput'),'Registration Number:'),length('Registration Number:')),sub(sub(IndexOf(variables('SPInput'),'Movement Recommended By:'),IndexOf(variables('SPInput'),'Registration Number:')),length('Movement Recommended')))

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,750)