Text Extraction

[DT3202]

Good Morning Everyone,
I have an issue where the clinical team is downloading csv files and manually copy/pasting summaries. The data from the system they use comes with text, symbols, and numbers in the cell. I only need the text and am trying to figure out how to extract it into another cell with a formula. Here is an example of the data:

 

[DanteAmor]

Try this UDF

Function ExtractText(c As Range) As String
  With CreateObject("VBSCRIPT.REGEXP")
    .Pattern = "[^a-z]"
    .Global = True
    .IgnoreCase = True
    ExtractText = WorksheetFunction.Trim(.Replace(c.Value, " "))
  End With
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ExtractText just like it was a built-in Excel function. For example,

Book1
	A	B
1	OAI:1:100,43,255:;Pentax:1:1 98,0,34:;Fujifilm:1:129	OAI Pentax Fujifilm
Sheet

Cell Formulas
Range	Formula
B1	B1	=ExtractText(A1)

 

[DT3202]

That works perfectly. Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[DT3202]

I've run into a bit of an issue. Is there a way I can capture Gastropscopes on Colonoscopes in the below? I am getting all of the lower case letter as well using the extracttext forumula, as you can see on the right.

 

[DanteAmor]

You could put the text instead of the image, that would help us to use the data, just copy the text from the cell and paste it here.
Also paste here the result you want.

 

[DT3202]

Here is the text:

35af8334-67ba-4020-b833-22a2b07408a3|Gown;0ffdc59f-6402-4a66-b3ba-cc02bef9ed13|Gloves;5f7af1d3-689d-48d4-b9c4-50aa30436003|Face shield;20c6ffd9-0dd1-4663-b99e-3860f493df6c|Hair covering;dd949532-1bbf-4d48-956d-4cde929003b1|Shoe covers

I would like:
Gown Gloves Face Shield Hair covering Shoe covers. There are a few with alphanumeric strings like this

 

[DanteAmor]

Always in all cells there is the pattern before each text the pipe "|" and ends with ";"

|Gown;
|Gloves;
|Face shield;

??

 

[DT3202]

yes. Each row with data starts with the string of numbers and letters followed by | and ending with ;

 

[sandy666]

maybe something like this

Column1
35af8334-67ba-4020-b833-22a2b07408a3|Gown;0ffdc59f-6402-4a66-b3ba-cc02bef9ed13|Gloves;5f7af1d3-689d-48d4-b9c4-50aa30436003|Face shield;20c6ffd9-0dd1-4663-b99e-3860f493df6c|Hair covering;dd949532-1bbf-4d48-956d-4cde929003b1|Shoe covers
Column1
Gown
Gloves
Face shield
Hair covering
Shoe covers

using Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Table.ReplaceValue(Source,"|","|@",Replacer.ReplaceText,{"Column1"}), {{"Column1", Splitter.SplitTextByAnyDelimiter({"|",";"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Replace = Table.ReplaceValue(Table.AlternateRows(Split,0,1,1),"@","",Replacer.ReplaceText,{"Column1"})
in
    Replace