Extract and Copy Text Between Repeating Characters

[mjcanty]

Hello! I am trying to extract and copy text that is in between the first and second colon ":" in each cell in column A and copy said text into column B. In this example, "California" is the text in between the first and second colon ":" but that will not always be the case. The problem that I am having is that that are additional colons that are in each cell. How would I achieve this? Thanks in advance!!

United States:California:Alameda:Oakland

United States:California:Alameda:Berkeley

United States:California:Alameda:San Leandro

United States:California:Alameda:Castro Valley

United States:California:Alameda:Dublin

United States:California:Alameda:Pleasanton

 

[Joe4]

Try this formula (for an entry in cell A1:

=TRIM(MID(SUBSTITUTE(A1,":",REPT(" ",100)),100,100))

 

[Rick Rothstein]

Does this formula work for you....

=TRIM(MID(SUBSTITUTE(A1,":",REPT(" ",99)),99,99))

 

[sandy666]

with Power Query

source		Result
United States:California:Alameda:Oakland		California
United States:California:Alameda:Berkeley		California
United States:California:Alameda:San Leandro		California
United States:California:Alameda:Castro Valley		California
United States:California:Alameda:Dublin		California
United States:California:Alameda:Pleasanton		California

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.AddColumn(Source, "Result", each Text.BetweenDelimiters([source], ":", ":"), type text),
    TSC = Table.SelectColumns(ETBD,{"Result"})
in
    TSC