Extract ALL instances of text string

[Webbers]

I have been searching and having not found what I need. Basically this will be added to a "data dump" file from one of our systems. we need to search one of the fields, and this field is a free form Notes field. So there is really no defined structure, hence my problem. I need the ability to extract a specific kind of criteria within this cell.

1) It will always begin CAS-
2) Currently there are 8 numbers after, but open cases may have 7 and we may be at 9 soon
3) The notes field MAY have no case #s listed, only 1, or there may be multiple (I have seen in excess of 20 cases listed
4) The case # can be listed anywhere within the notes field, there is 0 consistency from the users.

The Formula will be added to Column AS and the notes are in column AP. There are 2 note samples, Each note of different length with the Case # in a different location, and one example shows 2 cases. If there are multiple cases, I need them all to display in the extract. Can anyone help please?

A sample of a note:
Salary modified by training error. CAS-12345678, sent to Payroll

Another sample
DM resolved that "ee processed with 8/26 start date
CAS-22117755
Hire case 9/9/19
EDM resolved as "processed"
note from HR in CAS-66098778

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

[Webbers]

Glenn,

I have a sample of real data. I have removed data from some rows, as it was confidential and/or non-relevant. I have set up column AI with a "mock" results. In compiling the results, I realized the staff placing these case #s in multiple columns. I am not sure how difficult this is, but I am NOT opposed to having 4 "helpers" columns, one for each designated column that has this info. Then have one "final results" to pull everything together. Whatever is easiest.

The Case #s appear in the following columns: B, C, S, AB. The final result should look something like Column AI. The formula would go in column AH. This is a working model showing the variances... different column location, location within each cell, sometimes a "special character" is before the case (like a bullet). The only consistency is "CAS-" and the lack of consistency of the team. Anything you can do will be greatly appreciated! Thanks!

[NARAYANK991]

Hi ,

When there are multiple case numbers in one cell , what is the upper limit on the number of cases that can appear in one cell ?

Narayan

[kvsrinivasamurthy]

It is better to go for UDF. It is written with VBA code and can be used like Excel function. Is it ok.

[menem]

I think the same, UDF is the best way for this.

But if you still want to use only formula, many of helper cells will be used.
In this sample file it allow for 30 of occurs.

AN2 for combind all cell and remove un-wanted chars.

Please Login or Register  to view this content.

AO2 for 1st of CAS-

Please Login or Register  to view this content.

AP2 to BR2 for next of CAS-

Please Login or Register  to view this content.

AJ2 for output

Please Login or Register  to view this content.

Regards.

[Glenn Kennedy]

My effort. To keep the formula manageable, first create a Named range (called Joined). CTRL-F3 to view/edit:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B2&" "&Sheet1!$C2&" "&Sheet1!$S2&" "&Sheet1!$AB2,"CAS-","CAS¦"),"-"," "),"¦","-"),"•"," "),CHAR(10)," ")

Then, in AI2, copied across (as far as needed) and down:

=IFERROR(TRIM(LEFT(SUBSTITUTE("C"&MID(Joined, FIND("^", SUBSTITUTE(Joined, "CAS-", "^", COLUMNS($A:A)))+1, 100)," ",REPT(" ",100)),100)),"")

[Bosco]

To extract specific text begin with "CAS" complete with remove duplicate and return results in single cell

In AJ2, copied down :

=TEXTJOIN(CHAR(10),TRUE,FILTERXML("<a><b>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($AB2&" "&$C2&" "&S2&" "&B2,"-"," "),CHAR(10)," "),"#",),"•",),"CAS ","CAS-<r/>")," ","</b><b>")&"</b></a>","//b[r][not(following::*=.)]"))

Regards
Bosco

[Webbers]

Bosco,

I just tried that, and unfortunately it only pulled in the 1st case # for each record. Often times there are multiple case #s, and I need to find a way to be able to pull them all in. I am not "hung up" on the use of a formula for this. If you feel this would work better with a VBA script, I am okay going that route. My main concern is that if 5 cases are listed, I need to be able to pull all 5, not only the first.

[Glenn Kennedy]

Are you SURE Bosco's solution did not work? Enable text wrapping in the cells....

[Bosco]

See attached file in formula solution.

1] For Excel 2016 or below, in the using of TEXTJOIN UDF, so file saved in xlsm type

Select AJ2 >> Define name >>
>> Name : ExtractDataAndRemoveDuplicate
>> Refer to : =FILTERXML("<a><b>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$AB2&" "&Sheet1!$C2&" "&Sheet1!S2&" "&Sheet1!B2,"-"," "),CHAR(10)," "),"#",),"•",),"CAS ","CAS-<r/>")," ","</b><b>")&"</b></a>","//b[r][not(following::*=.)]")
>> OK

Then,

In AJ2, enter array formula (CSE) copied down and enable "Wrap Text":

=IFERROR(TEXTJOIN(CHAR(10),1,ExtractDataAndRemoveDuplicate),ExtractDataAndRemoveDuplicate)

2] For Office 365, in the using of TEXTJOIN function

In AJ2, enter array formula (CSE) copied down and enable "Wrap Text":

=TEXTJOIN(CHAR(10),1,FILTERXML("<a><b>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$AB2&" "&Sheet1!$C2&" "&Sheet1!S2&" "&Sheet1!B2,"-"," "),CHAR(10)," "),"#",),"•",),"CAS ","CAS-<r/>")," ","</b><b>")&"</b></a>","//b[r][not(following::*=.)]"))

Regards
Bosco

[Webbers]

Bosco----

Thanks so very much! Can see where I would use that functionality on multiple projects that I work. You are a lifesaver! The thought of having to pull all that case data manually scared me! Thanks again so very much!!!!!