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:

 

[kweaver]

Here's a UDF I cooked up:

Function GetString(c As Range) As String
 Dim GS As String
 GetString = ""
 GS = c.Value & ";"
While 0 <> Len(GS)
 GetString = GetString & Mid(Left(GS, WorksheetFunction.Find(";", GS) - 1), WorksheetFunction.Find("|", GS) + 1, Len(GS)) & " "
 GS = Right(GS, Len(GS) - WorksheetFunction.Find(";", GS))
Wend
GetString = Left(GetString, Len(GetString) - 1)
End Function

 

[DanteAmor]

Use this

Book1
	A	B
1	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	Gown Gloves Face shield Hair covering Shoe covers
Hoja2

Cell Formulas
Range	Formula
B1	B1	=ExtractText(A1)

Function ExtractText(c As Range) As String
  Dim cad As String, e As Variant
  For Each e In Split(c.Value & ";", ";")
    cad = cad & Mid(e, InStr(1, e, "|") + 1) & " "
  Next
  ExtractText = WorksheetFunction.Trim(cad)
End Function

 

[DT3202]

Here's a UDF I cooked up:

Function GetString(c As Range) As String
Dim GS As String
GetString = ""
GS = c.Value & ";"
While 0 <> Len(GS)
GetString = GetString & Mid(Left(GS, WorksheetFunction.Find(";", GS) - 1), WorksheetFunction.Find("|", GS) + 1, Len(GS)) & " "
GS = Right(GS, Len(GS) - WorksheetFunction.Find(";", GS))
Wend
GetString = Left(GetString, Len(GetString) - 1)
End Function

thanks! This did the trick!

 

[DT3202]

Use this

Book1
	A	B
1	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	Gown Gloves Face shield Hair covering Shoe covers
Hoja2

Cell Formulas
Range	Formula
B1	B1	=ExtractText(A1)

Function ExtractText(c As Range) As String
  Dim cad As String, e As Variant
  For Each e In Split(c.Value & ";", ";")
    cad = cad & Mid(e, InStr(1, e, "|") + 1) & " "
  Next
  ExtractText = WorksheetFunction.Trim(cad)
End Function

This works as well. Thanks for the help!

 

[DanteAmor]

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