Replace Existing Text

[RAHULBANDI]

Hi 

I have Following Data

NAME

Amalgamation

Annual Book Closure

Bonus Issue

Right Issue of Equity Shares

Right Issue of Equity Shares with Warrants

Right Issue of Fully Convertible Debentures

Expected OUT-PUT

NAME

Amalgamation

Annual Book Closure

Bonus Issue

Right Issue 

Right Issue 

Right Issue 

I'm looking only for right-issue to be converted

[mussaenda]

Select your column > Transform > Extract > Before Delimiter > Advance Options 

Delimiter put space

Number of Delimitiers to skip put 1

[RAHULBANDI]

Hi 

I have other rows in that data which are also getting splitted

NAME

Amalgamation                                                          Amalgamation   

Annual Book Closure                                                Annual Book 

Bonus Issue                                                                 Bonus Issue

Right Issue of Equity Shares                                        Right Issue

Right Issue of Equity Shares with Warrants                Right Issue

Right Issue of Fully Convertible Debentures               Right Issue

Exchange of Share Certificate                                     Excahnge of

Im looking replace only text which starts with Right Issue

[mussaenda]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hYyxCsJQDAB/JXT2J2xVcNXBoXRIIfYF0wTzEqV/r7Sr4HjHcX3f7GeUCWcMNm2G3VeoJgq0Zg/oxGo6rb41zQrnWnPjC08lNga7w/GZHAtcCzrVvwG8OQrc0B01fuanFFmgM32RB49CcKCRNHLdDx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Column1", each if Text.StartsWith(Text.Upper(_), "RIGHT ISSUE") then Text.BeforeDelimiter(_, " ", 1) else _, type text}})
in
    #"Extracted Text Before Delimiter"

Same procedure, 

Open Blank query, Advanced Editor, Select all delete and paste the code

[Anonymous · Accepted solution]

What if you try this?

= Table.AddColumn(Origen, "Custom", each let splitNAME = Splitter.SplitTextByDelimiter("Right Issue", QuoteStyle.None)([NAME]) in Text.Combine({Text.Start([NAME], 11), Text.Middle(splitNAME{0}?, 11)}), type text)

[RAHULBANDI]

thq very much its working