Text to Columns

(2) Share

Report

Posted on by KhrystinaM

158

Hello,

I am trying to launch an excel document, read the excel document and split a cell based on a special character (text to columns). I cannot figure out how to implement this piece. Could someone please help me?

Thank you,

Khrystina

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

Deenuji_Loganathan_ 6,255 Moderator on at

Like (2)

Report
Copy link

Link copied!

Hi KhrystinaM ,

Could you please share a screenshot of your Excel file to show how it looks? Additionally, could you share the desired output format?

Regards,

Deenu

Was this reply helpful? Yes No
KhrystinaM 158 on at

Like (1)

Report
Copy link

Link copied!

I'm trying to separate the text in column a at the | and adding it to column B.

Was this reply helpful? Yes No
Suggested answer

eetuRobo 4,537 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
There might be a cleaner way with less actions but here is how I got that to work:

Result:

Code:
File.ReadTextFromFile.ReadTextAsList File: $'''C:\\Users\\YourPath\\File.txt''' Encoding: File.TextFileEncoding.UTF8 Contents=> FileContents Text.Replace Text: FileContents TextToFind: $'''(?<=\\|)[^\\r\\n]*''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> aColumn Text.Replace Text: aColumn TextToFind: $'''|''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> aColumn Text.Replace Text: FileContents TextToFind: $'''.*(?=\\|)''' IsRegEx: True IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> bColumn Text.Replace Text: bColumn TextToFind: $'''|''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> bColumn Text.SplitText.Split Text: aColumn StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> Alist Text.SplitText.Split Text: bColumn StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> Blist Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Alist Column: $'''A''' Row: 1 Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Blist Column: $'''B''' Row: 1

EDIT:
This way also works:

aColumn Regex: (?<=\|)[^\r\n]*
bColumn Regex: .*(?=\|)

Was this reply helpful? Yes No
KhrystinaM 158 on at

Like (1)

Report
Copy link

Link copied!

What did you put in the Relace with field? Your screenshot looks like it's a space or empty, but my system is not letting me do that.

Was this reply helpful? Yes No
Suggested answer

eetuRobo 4,537 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

In Power Automate Desktop empty syntax is %''%
Also remember to toggle on "Use regular expression for find and replace" since this is regex we are using and not plain text.

Also if you use the second approach Remove item from list (remove all empty items so With value: %''%) remember to turn on "On error" and "Continue to next action"

This way if there is no empty items it wont fail.

Was this reply helpful? Yes No