Find a 7 character code in text strings of varying length

GavAdams

New Member
Joined
Nov 2, 2016
Messages
7
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
 
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
Thank you, yeah I did it in quite a rush.

Here's the image:
btruaGy.png
 
Upvote 0
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)
 
Upvote 0
just for fun with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIGI_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]DIF3790[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/DIF3790/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td]DIF3790[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOO011/OBJ_SKU_FAMILY_DIF379O/DIF324O/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]DIF324O[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOO011/OBJ_SKU_FAMILY_DIF379O/DIF324O/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td]DIF324O[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/DIF3750/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]DIF3750[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_DIF3790/DIF3250/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td]DIF3250[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOOOH/OBJ_SKU_FAMILY_DIR15GO/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]DIR15GO[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0730/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>[/td][td][/td][td]DIF0730[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0230/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>[/td][td][/td][td=bgcolor:#E2EFDA]DIF0230[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_AOO011/OBJ_SKU_FAMILY_11831296/DIFO7GO/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>[/td][td][/td][td]DIFO7GO[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0770/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>[/td][td][/td][td=bgcolor:#E2EFDA]DIF0770[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0240/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>[/td][td][/td][td]DIF0240[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0740/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>[/td][td][/td][td=bgcolor:#E2EFDA]DIF0740[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00011/OBJ_SKU_FAMILY_11831296/DIF0250/data/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>[/td][td][/td][td]DIF0250[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2900/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td]BRI2900[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]/Home/CLH_DIG l_CAT_A00002/CLH_DIGI_SUB_A00012/door-frames/0BJ_SKU_FAMILY_1791S035/BRI2100/data/XXXXXXXXXXXXXXXXXXXXXXX[/td][td][/td][td=bgcolor:#E2EFDA]BRI2100[/td][/tr]
[/table]


Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top