Please help me with a formula - easy to understand

[Fernandez30]

Hi,

Can somebody please help me with a formula, which is easy for me to understand.

I need a formula that would extract the highlighted data in B2 & C2.

 

[AlanSidman]

Use the Text to columns function on the Data Tab. Set your delimeter to ":"

Look at this link

https://www.excel-easy.com/examples/text-to-columns.html

 

[Fernandez30]

Use the Text to columns function on the Data Tab. Set your delimeter to ":"

Look at this link

https://www.excel-easy.com/examples/text-to-columns.html

I dont want to use text to column; I would like to use a formula so it easier that way

 

[Somendra Misra]

Hi,

Text formulas are tricky some time and depends heavily on the input data. The format and pattern of data to be uniform across all cells to get consistent results. If the data in your sample is consistent than you can use below formula.



For Root Cause : =MID(C4,FIND($D$3&": ",C4)+LEN($D$3)+2,FIND(CHAR(10),C4)-LEN($D$3)-2)
For Sub-root Cause: =MID(C4,FIND($E$3&": ",C4)+LEN($E$3)+2,FIND(CHAR(10),C4)+LEN($E$3)-2)

refer the cells as shown in the pic above.

Regards,

 

[Fernandez30]

Hi,

Text formulas are tricky some time and depends heavily on the input data. The format and pattern of data to be uniform across all cells to get consistent results. If the data in your sample is consistent than you can use below formula.

View attachment 67054

For Root Cause : =MID(C4,FIND($D$3&": ",C4)+LEN($D$3)+2,FIND(CHAR(10),C4)-LEN($D$3)-2)
For Sub-root Cause: =MID(C4,FIND($E$3&": ",C4)+LEN($E$3)+2,FIND(CHAR(10),C4)+LEN($E$3)-2)

refer the cells as shown in the pic above.

Regards,

Thanks a lot Somendra for helping me with this, truly appreciate it.

 

[Peter Bartholomew]

A taste of the future using Office 365 insider
= IFERROR( LET(
start, 1 + FIND( header, Comments ) + LEN( header ),
end, IFERROR( FIND( CHAR(10), Comments, start ), LEN(Comments) ),
MID( Comments, 1+ start, end-start )
), "" )

In other versions of Excel it is possible to use named formulas 'start' and 'end' and the formula is as shown in the fourth row.

The formula matches each header to the comment tags. One formula cell returns the entire table as an array.