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]

Sub SplitLF()
Dim sData As String, sStr() As String, i As Integer, j As Integer, LR As Long, nStr As String, ubnd As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For j = 1 To LR
sData = Range("A" & j)
sStr = Split(sData, Chr(10))
ubnd = UBound(sStr)
For i = 1 To ubnd
nStr = Right(sStr(i), Len(sStr(i)) - (InStr(sStr(i), ":") + 1))
Cells(j, i + 2) = nStr
Next i
Next j
End Sub

ALT+F11 will open the VBA editor. Right click This Workbook and insert a module. Paste this code in there.
Then run it from the Developer tab under Macros.

 

[DanteAmor]

Power Details:-

I guess you don't want the "-".

Try this in cell B2 and copy it across

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"-"&CHAR(10),""),CHAR(10),REPT(" ",LEN($A2)),COLUMNS($B:B)),": ",REPT(" ",LEN($A2)),COLUMNS($B:B)),LEN($A2)+1,LEN($A2)))

 

[nemo1478]

It works! I never knew excel was this powerful. I have been typing in these fields manually for months. I just have to take some time and try to understand this formula. Thank you all for your help.

 

[Rick Rothstein]

To modify the formula I posted (see the assumptions I made in Message #6), you just need to change the -1 to +1...

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

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[nemo1478]

I'm having a slight problem with one of the solutions presented. The following formula from DanteAmor works perfectly

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"-"&CHAR(10),""),CHAR(10),REPT(" ",LEN($A2)),COLUMNS($B:B)),": ",REPT(" ",LEN($A2)),COLUMNS($B:B)),LEN($A2)+1,LEN($A2)))

but this one from Rick Rothstein has a slight issue:

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

1. Formula 2 is extracting 'Time of A failure:' into one of the columns when I actually want the info after the colon which in the example should be 'y'.