Need to pull data from the middle of the string

[smalik]

Is there is a simple formula that would let me pull a three letter or a four letter code from the middle of the string?

Column A shows the data dump from the system
Column B is my attempt to pull the code in a multi step process. (very inefficient way to do it.... I am sure there is a better way)
Column C is what I need (I manually typed the results for the purpose of this post)

The word "Policy" will always be the beginning of the string followed by a blank space, then "-", then blank space, then a code
Code can only be either 3 letters or 4 letters
Result column will show a blank cell if the string does not begin with the word "Policy" like in row 3 or 12-16

Any help is greatly appreciated.

 

[Joe4]

Try this for row 1 and copy down:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))>1,TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",100)),100,100)),"")

 

[mumps]

=IF(LEFT(A1,6)="Policy",MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1),"")

 

[smalik]

Try this for row 1 and copy down:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))>1,TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",100)),100,100)),"")

Thank you.... I understand the first part but what is the last part doing? "REPT(....." i.e., what is the significance of 100? and REPT?

 

[Joe4]

Basically, it is replacing all incidences of " - " (space, dash, space) with 100 spaces. So then you are putting 100 blank spaces between the section before and after the part you want to extract (REPT is the REPEAT function, saying to repeat a single space 100 times).

Then, we are starting at space 100 of that, and taking the next 100 spaces. That will get us our 3 or 4 character code (and a whole lot of spaces).
So then we just use the TRIM function to get rid of ll the extra spaces.

Make sense?

 

[smalik]

=IF(LEFT(A1,6)="Policy",MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1),"")

Thank you the formula works fine but in order for me to understand for future, can you please help me understand what is this formula doing?

I broke it down and figured out that first "Find" syntax is looking at the 9th character. The second syntax is subtracting and coming to result of 6 but I have no idea what is the significance of 6

 

[smalik]

Basically, it is replacing all incidences of " - " (space, dash, space) with 100 spaces. So then you are putting 100 blank spaces between the section before and after the part you want to extract (REPT is the REPEAT function, saying to repeat a single space 100 times).

Then, we are starting at space 100 of that, and taking the next 100 spaces. That will get us our 3 or 4 character code (and a whole lot of spaces).
So then we just use the TRIM function to get rid of ll the extra spaces.

Make sense?

This does make sense. Thank You very much.

 

[mumps]

The 6 simply refers to the number of characters in the string "Policy".

 

[sandy666]

another option

RawData		Result
Policy - AVIA - UA00014882AV18A		AVIA
Policy - AVIA - UA00014882AV18A		AVIA
Account - 3 Guys Beverages LLC
Policy - MGL - MLB-0000182-01		MGL
Policy - MEL - RG1900078		MEL
Policy - EXLI - RN1900239		EXLI
Policy - CLL - MLB-0000183-01		CLL
Policy- VPL - V-16206-19		VPL
Policy- EXLI -RN1900291		EXLI
Policy - PKGC - PK-19-040		PKGC
Policy - PKGC - PK-19-040		PKGC
Account - 3U Technologies, LLC
Account - 3U Technologies, LLC
Account - 3U Technologies, LLC
Account - 69th National Square Dance Convention
Account - 69th National Square Dance Convention

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.SelectColumns(Table.AddColumn(Source, "Result", each if Text.StartsWith([RawData], "Policy") then Text.BetweenDelimiters([RawData], "- ", " -") else null),{"Result"})
in
    Result

 

[Joe4]

This does make sense. Thank You very much.

You are welcome. Glad we could help!