Split 2 Column Array

[HebbyHebz]

Hello, Good day, 

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

abc@domain.com.au	Fiona
def@domain.com.au	Adeola
ghi@domain.com.au	Bob
jkl@domain.com.au	Jack
mno@domain.com.au	Tracy

Or better shown:

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.

[ekarim2020]

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

The flow would resemble something like this:

Hope this helps.

Ellis

[HebbyHebz]

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.

[ekarim2020]

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:

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:

Ellis

[ekarim2020 · Accepted solution]

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

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

Split the data at the new line:

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"

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

[HebbyHebz]

@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!