Extracting and adding text

Long time lurker for information and first time poster.

Been getting more involved in Excel the past few years and am nowhere near extremely proficient with it yet, but have been beating my head against the wall on this and I'm sure I'm overcomplicating it. I've inherited a data set used for tracking production on an industrial construction sit from a previous employee of the company I work for. We're given a cable ID tag which I'd like to strip down to it's individual Input/Output (I/O) identifier for tracking purposes. I've attached a small excerpt from the data for reference along with the text I'd like it to display in column I. What's currently being displayed is in column H. The instruments have letter identifiers that are two to three characters followed by 3-5 numbers for the device number. Everything I've done so far has had to have me adjust for devices with only two letters or with more than 3 numbers and I know there has to be a better way of doing this to save me time with 2,000+ lines.

Any help is very very much appreciated and I'm looking forward to developing my skills more in this community.

One way,

=LET(
  a,FILTER(H2:H100,H2:H100<>""),
  DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,
   LET(
     b,MIN(IFERROR(SEARCH(SEQUENCE(10,,0),y),100)),
     c,TEXTBEFORE(y,"-"),
     LEFT(c,b-1)&"-"&MID(c,b,10)
   )
 ))),1)
)

Originally Posted by windknife

One way,

=LET(
  a,FILTER(H2:H100,H2:H100<>""),
  DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,
   LET(
     b,MIN(IFERROR(SEARCH(SEQUENCE(10,,0),y),100)),
     c,TEXTBEFORE(y,"-"),
     LEFT(c,b-1)&"-"&MID(c,b,10)
   )
 ))),1)
)

Thanks for the great work! Trying to understand the work you've done and I see you used H column. Would this be the same if you used the cable id column A instead? Sorry if this is a bumd question. Just trying to learn and never done anything to this degree.

If you want to use column A instead of column H, try this.

=LET(
  a,TEXTAFTER(FILTER(A2:A100,A2:A100<>""),"-"),
  DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,
   LET(
     b,MIN(IFERROR(SEARCH(SEQUENCE(10,,0),y),100)),
     c,TEXTBEFORE(y,"-"),
     LEFT(c,b-1)&"-"&MID(c,b,10)
   )
 ))),1)
)

Originally Posted by alansidman

An alternative solution using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Text Before Delimiter" = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([#"I/O"], "-"), type text),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Inserted Text Before Delimiter", "Text Before Delimiter", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Text Before Delimiter.1", "Text Before Delimiter.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Text Before Delimiter.1", "Text Before Delimiter.2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"I/O New")
in
    #"Merged Columns"

Originally Posted by windknife

If you want to use column A instead of column H, try this.

=LET(
  a,TEXTAFTER(FILTER(A2:A100,A2:A100<>""),"-"),
  DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,
   LET(
     b,MIN(IFERROR(SEARCH(SEQUENCE(10,,0),y),100)),
     c,TEXTBEFORE(y,"-"),
     LEFT(c,b-1)&"-"&MID(c,b,10)
   )
 ))),1)
)

Thank you both for your help. I'm getting a #N/A error when trying to import the work into my data set. My time is limited so I'm going to have to hand do it until I get enough time to learn and understand these operations better. Thank you again!

Originally Posted by windknife

If you want to use column A instead of column H, try this.

=LET(
  a,TEXTAFTER(FILTER(A2:A100,A2:A100<>""),"-"),
  DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,
   LET(
     b,MIN(IFERROR(SEARCH(SEQUENCE(10,,0),y),100)),
     c,TEXTBEFORE(y,"-"),
     LEFT(c,b-1)&"-"&MID(c,b,10)
   )
 ))),1)
)

Windknife,

I've had time to work through this and try to troubleshoot it but I still cant get it to work correctly. Do you have any ideas why I might be getting a #N/A error?

Upload a sample file with error message.