Find a 7 character code in text strings of varying length

[GavAdams]

Hi,

I've a list of item paths, each with a product code in them. The product code is always in the format AAA1111, but it's in a different place in each string, and each string is a differing length.

Can anyone think of a forumla that will extract that 7 character code? I have 6,500 lines so manual is not an option.

I've spoofed some X's into the end of the string to protect some of the data, but you can see there is always a code in the above format:


Thanks

 

[Peter_SSs]

I removed that failed image code. Perhaps you can just type a few examples and the expected results. Otherwise, my signature block below has help on how to post some small sample data.

 

[GavAdams]

Thank you, yeah I did it in quite a rush.

Here's the image:

 

[Peter_SSs]

Here's the image:

Unfortunately we cannot copy such image data to our sheets to test with, hence my other suggestion.

However, if the code is always followed by "/data/" as in all your samples, try this formula

=RIGHT(LEFT(A2,SEARCH("/data/",A2)-1),7)

 

[sandy666]

just for fun with PowerQuery

Column1		Column1
/Home/CLH_DIGI_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX		DIF3790
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/DIF3790/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX		DIF3790
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOO011/OBJ_SKU_FAMILY_DIF379O/DIF324O/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX		DIF324O
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOO011/OBJ_SKU_FAMILY_DIF379O/DIF324O/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX		DIF324O
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/DIF3750/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX		DIF3750
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/DIF3250/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX		DIF3250
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOOOH/OBJ_SKU_FAMILY_DIR15GO/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX		DIR15GO
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0730/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>		DIF0730
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0230/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>		DIF0230
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOO011/OBJ_SKU_FAMILY_11831296/DIFO7GO/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>		DIFO7GO
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0770/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>		DIF0770
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0240/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>		DIF0240
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0740/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>		DIF0740
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0250/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>		DIF0250
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2900
/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2100/data/XXXXXXXXXXXXXXXXXXXXXXX		BRI2100

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Between = Table.TransformColumns(Source, {{"Column1", each Text.BeforeDelimiter(_, "/data"), type text}}),
    Last7 = Table.TransformColumns(Between, {{"Column1", each Text.End(_, 7), type text}})
in
    Last7[/SIZE]

 

[GavAdams]

Thank you both for your help, got it sorted.

 

[sandy666]

You are welcome

Have a nice day

 

[Peter_SSs]

Thank you both for your help, got it sorted.

Cheers. Glad to help.