Extract contents between two sub strings.

[nemo1478]

Reserve power at site: x
Time of A Failure: y
Time of A Restoration: z
Duration of A Failure: a
Time of Site Failure: b
Time of Site Restoration: c
Duration of Site Failure: d
Duration of Reserve Power: e

In the above sample text I want to be able to extract the data (x,y,z,a,b,c,d,e) between the various sub strings and place them into separate columns. I have found solutions that will extract the contents between two words or two characters but not between entire strings of text. Can anyone suggest how this can be done or an even better way of approaching this?

 

[kweaver]

Welcome to Mr. Excel.
In your example, are those lines separate strings in, say, the A column, 8 rows?
Are you wanting to extract whatever is AFTER the colon on each line and put them in 8 subsequent columns?

 

[nemo1478]

It's 8 rows in 1 cell (really 1 long string with word wrap applied) and yes I just want to extract what is after the colon on each line in 8 other columns

 

[kweaver]

It looks more like each line ends with an ALT+Enter -- is that the case?

 

[nemo1478]

Yes that's correct

 

[Rick Rothstein]

Assuming your first data cell is A1 and that a data cell always contains less than 300 characters total, then put this formula in cell B1 and copy it across...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,CHAR(10),":"),":",REPT(" ",300)),(2*COLUMNS($B1:B1)-1)*300,300))

 

[DanteAmor]

Here another formula for you to consider:

varios 14feb2020.xlsm
	A	B	C	D	E	F	G	H	I
2	Reserve power at site: x Time of A Failure: y Time of A Restoration: z Duration of A Failure: a Time of Site Failure: b Time of Site Restoration: c Duration of Site Failure: d Duration of Reserve Power: e	x	y	z	a	b	c	d	e
sheet

Cell Formulas
Range	Formula
B2:I2	B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,CHAR(10),REPT(" ",LEN($A2)),COLUMNS($B:B)),":",REPT(" ",LEN($A2)),COLUMNS($B:B)),LEN($A2)+1,LEN($A2)))

 

[kweaver]

My amateur VBA approach so that you don't have to drag the formula to the right.
Assumes original data is in A1 (adjust accordingly). A VBA ACE here will make this considerably easier, I'm sure.

Sub SplitLF()
Dim sData As String, sStr() As String, i As Integer, nStr As String, ubnd As Variant
sData = Range("A1")
sStr = Split(sData, Chr(10))
ubnd = UBound(sStr)
For i = 0 To ubnd
 nStr = Right(sStr(i), Len(sStr(i)) - (InStr(sStr(i), ":") + 1))
 Cells(1, i + 2) = nStr
Next
End Sub

 

[nemo1478]

You guys work fast! I just made one mistake. I left out the very first line of the cell which I have now included below. I think this will affect the formula.

Power Details:-
Reserve power at site: x
Time of A Failure: y
Time of A Restoration: z
Duration of A Failure: a
Time of Site Failure: b
Time of Site Restoration: c
Duration of Site Failure: d
Duration of Reserve Power: e

 

[nemo1478]

kweaver I really really appreciate the effort and time you took to respond to my problem but honestly I don't have a clue how to use that code in the excel spreadsheet.