Help with Finding Instances of Several Similar Texts in one cell

[narbehs]

Hi,

I have an excel workbook that is an export from Azure Devops. The Description field has some useful information but has a lot of info in it (paragraphs and sometimes even tables).
I want to extract anytime there is a value for BR.#### . For example Business Rule # 4555 would be BR.4555. In that same cell there could be a BR.0001, BR.0002 (max I've counted is 20).

I want a formula that lists / extracts all BR.#### that exist in that cell.

Is that doable?
Thank you!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[nick.williams]

Something like this perhaps? As stated, a sample workbook would be useful.

=LET(values,MID(A1,SEQUENCE(LEN(A1)-6),7),FILTER(values,(LEFT(values,3)="BR.")*ISNUMBER(RIGHT(values,4)+0)))

[narbehs]

Thank you.
I'm having a hard time translating your work. Would the sample worksheet I provided make a difference?
I've never used LET before and not sure I'm replacing the variables correctly.

[nick.williams]

You should just need to replace "A1" with whatever cell you are referencing. But also I guess you want the results to display horizontal rather than vertical so I tweaked the SEQUENCE formula too. And I added in something so it returns "-" if there are no results. Try this in C2, then copy down:

=LET(values,MID(B2,SEQUENCE(1,LEN(B2)-6),7),FILTER(values,(LEFT(values,3)="BR.")*ISNUMBER(RIGHT(values,4)+0),"-"))

I notice this also returns BR.533 in your example. I don't know if that's a typo or if you might have 3-digit codes in your real data. If you need to exclude things like that then the safest way might be to test each of the 4 digits separately, although it gets a bit clunky:

=LET(values,MID(B2,SEQUENCE(1,LEN(B2)-6),7),FILTER(values,(LEFT(values,3)="BR.")*ISNUMBER(MID(values,4,1)+0)*ISNUMBER(MID(values,5,1)+0)*ISNUMBER(MID(values,6,1)+0)*ISNUMBER(MID(values,7,1)+0),"-"))

[Glenn Kennedy]

You were asked to provide a sheet which containd some expected results. You provided a sheet, but no expected results.

So, a guess:

=IFERROR(TRANSPOSE(FILTERXML("<A><B>"&SUBSTITUTE(B2,CHAR(10),"</B><B>")&"</B></A>","//B[starts-with(., 'BR.')]")),"")

copied down.

[JEC.]

With a UDF

Please Login or Register  to view this content.

Use as

Please Login or Register  to view this content.

[sandy666]

with Power Query

ID	List.1	List.2	List.3
5002	BR.0001	BR.533	BR.0002
5003	BR.0003

Please Login or Register  to view this content.

[narbehs]

Thank you.
I did sanitize the sample quite a bit so trying to translate your work.
Could you clarify what "values" are used as in your formula? Looking into LET in YT I'm not able to make the transition.

[narbehs]

Thank you, your guess was pretty spot on. Appreciate the help.
The data I have seems to be a lot more unstructured (was html at some point) so that might be causing issues in getting it to work on my end.

[Glenn Kennedy]

Who have you been talking to?

[narbehs]

Who have you been talking to?

One was in reply to your solution. Didn't use the "reply with quote" option. Still getting used to UI.

[Glenn Kennedy]

OK. So post a realistic sample and I'll have another look tomorrow.

Don't bother with reply with quote... just mention the name of the person you're addressing.

[narbehs]

You should just need to replace "A1" with whatever cell you are referencing. But also I guess you want the results to display horizontal rather than vertical so I tweaked the SEQUENCE formula too. And I added in something so it returns "-" if there are no results. Try this in C2, then copy down:

=LET(values,MID(B2,SEQUENCE(1,LEN(B2)-6),7),FILTER(values,(LEFT(values,3)="BR.")*ISNUMBER(RIGHT(values,4)+0),"-"))

I notice this also returns BR.533 in your example. I don't know if that's a typo or if you might have 3-digit codes in your real data. If you need to exclude things like that then the safest way might be to test each of the 4 digits separately, although it gets a bit clunky:

=LET(values,MID(B2,SEQUENCE(1,LEN(B2)-6),7),FILTER(values,(LEFT(values,3)="BR.")*ISNUMBER(MID(values,4,1)+0)*ISNUMBER(MID(values,5,1)+0)*ISNUMBER(MID(values,6,1)+0)*ISNUMBER(MID(values,7,1)+0),"-"))

Thank you.
I did sanitize the sample quite a bit so trying to translate your work.
Could you clarify what "values" are used as in your formula? Looking into LET in YT I'm not able to make the transition.

[narbehs]

Glenn Kennedy
Thank you. It's sensitive company stuff. I'll just mark it as solved. I think I'm almost there using the formulas and modifying it enough.

[nick.williams]

The LET formula allows you to define variables (or names) which can be re-used. This can shorten the formulae, make them easier to edit, and improve efficiency, in the right situations. The format is

=LET(name1,name_value1,calculation)

You can have as many names as you want, each one must be paired with a value, and the calculation is always the last item.

In my solution above it defines the name "values" as MID(B2,SEQUENCE(1,LEN(B2)-6),7). This returns an array of strings, each one 7 characters long, and each one starting 1 character further on than the last, to cover the whole of cell B2.

The calculation part is then FILTER(values,(LEFT(values,3)="BR.")*ISNUMBER(MID(values,4,1)+0)*ISNUMBER(MID(values,5,1)+0)*ISNUMBER(MID(values,6,1)+0)*ISNUMBER(MID(values,7,1)+0),"-")). This uses the FILTER function to return only the 7 character strings which begin in "BR." and end with 4 numeric characters. You can write the formula without the LET but it would get a lot longer as every place where it says "values" you would need to replace with "MID(B2,SEQUENCE(1,LEN(B2)-6),7)". It would also run slower (though this may not be noticeable depending on the scale of the sheet).

Does that help?