Get Position of First Text Char in Number & Text mixed String Power Query

[radonwilson]

1. How to get the positions of the first text char in a mixed string?

Master_Order_Report.xlsx
	A	B
7	Files Names	Output (position of first text char)
8	1112. Orders	6
9	2451 2 . Refunds	9
10	#784&@ . Cancellation	9
Sheet1

Cell Formulas
Range	Formula
B8	B8	=SEARCH("O",A8)-1
B9	B9	=SEARCH("R",A9)-1
B10	B10	=SEARCH("C",A10)-1

2. How to use these splitter functions with Text.Split() . I want to split the text and keep that in a list in Custom Column.

For Example, if there is a word "Sweet" I want to split it by the number of characters as 1 and keep that in a list {S,w,e,e,t}

 

[JEC]

Text.ToList is what you need

 

[alansidman]

Here is one way for first item. I think JEC has offered up the solution for the last sentence requested.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Files Names", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Files Names.1", "Files Names.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Text.Length([Files Names.1])+1),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom", "Merged", each Text.Combine({[Files Names.1], [Files Names.2]}, " "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Files Names.1", "Files Names.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Merged", "Custom"})
in
    #"Reordered Columns"

 

[smozgur]

Another way for #1 (since #2 is already answered and this method also uses Text.ToList() that explains how it works):
(Assumption from the sample: Text character = Letters: A to Z and a to z)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0NNJT8C9KSS0qVorViVYyMjE1VDBS0FMISk0rzUuBCCqbW5ioOQAFnRPzklNzchJLMvPzlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Files Names" = _t]),
    // Used binary data as the Source for testing purposes.
    // Delete the previous Source line and set up your source as you need, i.e. from a table/range, as shown below.
    // Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.AddColumn(Source, "Output (position of first text char)",
                each List.PositionOf(
                    List.Transform(
                            Text.ToList(_[Files Names]),
                            each List.Contains({"A".."Z"}, Text.Upper(_))
                        ),
                        true
                    )
                )
in
    Result

The result:

Files Names	Output (position of first text char)
1112. Orders	6
2451 2 . Refunds	9
#784&@ . Cancellation	9