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
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (3,233)