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)

Suggested answer

Deenuji_Loganathan_ 6,250 Super User 2025 Season 2 on at

Like (2)

Report

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

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,204 Super User 2025 Season 2 on at

Like (2)

Report
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

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,204 Super User 2025 Season 2 on at

Like (2)

Report

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