Skip to main content

Notifications

Community site session details

Community site session details

Session Id : QT0i1kbuzq5qfBgr8tOFMa
Power Automate - Building Flows
Answered

Split 2 Column Array

Like (0) ShareShare
ReportReport
Posted on 26 Aug 2021 09:31:37 by 44

Hello, Good day, 

 

HebbyHebz_0-1629906779967.png

After copying and pasting Cells A2:B6 from the Excel Spreadsheet in the screenshot above, the text output looks this:

 

abc@domain.com.auFiona
def@domain.com.auAdeola
ghi@domain.com.auBob
jkl@domain.com.auJack
mno@domain.com.auTracy

 

Or better shown:

Image.jpg

 
 

How can I split this array (it's an array right?) of rows so that I can send an email to each of these people and put the corresponding name in the body? Ideally in a loop (maybe apply to each?)

 

I'll need the Email and corresponding name extracted from the pasted data for each "Send a Mail" instance.

  • HebbyHebz Profile Picture
    44 on 26 Aug 2021 at 16:58:51
    Re: Split 2 Column Array

    @ekarim2020  You're a Super Hero! đź”Ąđź”Ąđź”Ą

     

    Really appreciate the detailed explanation and screenshots.

    Thanks for giving your time to this. I'm going to try this straight away.

     

    Thanks again!

     

     

  • Verified answer
    Ellis Karim Profile Picture
    10,936 Super User 2025 Season 1 on 26 Aug 2021 at 16:45:12
    Re: Split 2 Column Array

    Example flow, where the data was copied directly from Excel into the string:

    ekarim2020_0-1629995663335.png

    Set variables to hold the new line and (horizontal) tab characters:

    ekarim2020_1-1629995728734.png

    Split the data at the new line:

    ekarim2020_3-1629995834739.png

    The result of the split action is an array:
    [
      "123@ad.eu.local\tPerson1",
      "456@ad.eu.local\tPerson2",
      "789@ad.eu.local\tPerson3",
      "abc@ad.eu.local\tPerson4",
      "cdef@ad.eu.local\tPerson5"
    ]

    For each array item we extract the email address and person's name bu spitting the string at the tab character

    "123@ad.eu.local\tPerson1"

    ekarim2020_4-1629996015707.png

     

     

    NewLine Character: 
    decodeUriComponent('%0A')
    
    Tab Character:
    decodeUriComponent('%09')
    
    Compose Find NewLines:
    split(variables('varData'),variables('NewLineChar'))
    
    Set variable varEmail
    first(split(item(),variables('TabChar')))
    
    Set variable varPersonName
    Last(split(item(),variables('TabChar')))

     

    Hope this helps.

    Ellis

  • Ellis Karim Profile Picture
    10,936 Super User 2025 Season 1 on 26 Aug 2021 at 15:51:32
    Re: Split 2 Column Array

    The text that you copied from Excel is just seen as block of text and not as an array. It is likely that the email and name is separated by a tab character, and each row item is delimited with a CarriageReturn LineFeed character - if you have done a direct copy from Excel:

     

    ekarim2020_1-1629992820851.png

     

    You would need to split the string at the CR/LF (i.e. end of the line) to get reach row item, then split each row item at the tab character giving you the email address and the person's name.

    An array would resemble this format:

    ekarim2020_0-1629992688651.png

    Ellis

  • HebbyHebz Profile Picture
    44 on 26 Aug 2021 at 15:26:55
    Re: Split 2 Column Array

    Thanks @ekarim2020  this would definitely work well, although I was looking for a way to do this without the Excel connector, as connecting the tablet to the Excel Connector may not be possible in my scenario. 

     

    Looking for a way to do this with maybe just string functions.

     

    Nice Solution @ekarim2020 ! Thank you.

  • Ellis Karim Profile Picture
    10,936 Super User 2025 Season 1 on 26 Aug 2021 at 12:15:24
    Re: Split 2 Column Array

    You can read the Excel file contain your mailing list into Power Automate and send an email to each of the named recipients:

    ekarim2020_0-1629979876163.png

    The flow would resemble something like this:

    ekarim2020_1-1629980031284.png

    Hope this helps.

    Ellis

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Building Flows

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 18 Super User 2025 Season 1

#2
stampcoin Profile Picture

stampcoin 16

#3
Churchy Profile Picture

Churchy 12

Overall leaderboard
Loading started