Find a 7 character code in text strings of varying length

G

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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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
GavAdams said:
Here's the image:
Click to expand...
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
You must log in or register to reply here.

Similar threads

D
  • Question Question
Sort varying length table by custom conditions
Replies
0
Views
255
dewy0823
D
M
  • Question Question
Power Query with Dynamic Data Source
Replies
4
Views
927
Alex Blakenburg
A
S
  • Question Question
Extracting text within varying length phrases
Replies
8
Views
1K
Peter_SSs
Peter_SSs
L
  • Question Question
Importing Outlook Data
Replies
0
Views
658
Legacy 512328
L
J
  • Question Question
Augmenting VBA to Refresh Specific before All Power Queries
Replies
2
Views
433
johnny51981
J

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

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