Extract A String Between Two Characters

[theaudioguy]

I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

 

[ScomDev]

Thank you so much Rick! It works perfectly and I appreciate the simplicity of the formula. You rock!

 

[ScomDev]

Also if somebody else is interested by the same problem, it is possible to use Rick's formula slightly altered to make it work with columns like that

Description 1 Description 2 Description 3 Description 4 Description 501-30-2017 10:44:14.2313 TYPE_INFORMATION MessageInformation.SomeInformation Description of the record : MSRV A221 More description in text

Original text in A1, copy this in A2 and drop on the right.
=TRIM(MID(SUBSTITUTE("|"&$A1,"|",REPT(" ",LEN($A1))),COLUMNS($A:A)*LEN($A1),LEN($A1)))

 

[Pr0metheus]

Hello guys,
can anyone help with slightly different formula? Text is all-in-one, and needs to be separated in 5 columns according to header. There are 6, 5, 9 and 11 spaces between each text field. Thank you for help.

Date Time Level Source Event
7.2.2017 3:24:45 Warning KEPServerEX\Runtime Device 'A_VS20_St050.FX3U' has been auto-demoted.
7.2.2017 3:24:45 Warning KEPServerEX\Runtime Device 'B_St020.FX3U' has been auto-demoted.
7.2.2017 4:14:21 Information KEPServerEX\Runtime Kepware Communications Server 5.4
7.2.2017 4:14:23 Information Mitsubishi Ethernet Mitsubishi Ethernet Device Driver V5.4.135.0


-- removed inline image ---

 

[snb_]

Why don't you just start your own thread ?

 

[Pr0metheus]

Why new thread? It's still same thread question about extracting string between characters.

 

[István Hirsch]

Hello guys,
can anyone help with slightly different formula? Text is all-in-one, and needs to be separated in 5 columns according to header. There are 6, 5, 9 and 11 spaces between each text field. Thank you for help.

Date Time Level Source Event
7.2.2017 3:24:45 Warning KEPServerEX\Runtime Device 'A_VS20_St050.FX3U' has been auto-demoted.
7.2.2017 3:24:45 Warning KEPServerEX\Runtime Device 'B_St020.FX3U' has been auto-demoted.
7.2.2017 4:14:21 Information KEPServerEX\Runtime Kepware Communications Server 5.4
7.2.2017 4:14:23 Information Mitsubishi Ethernet Mitsubishi Ethernet Device Driver V5.4.135.0



-- removed inline image ---

Please mark where each substring begins.

 

[Peter_SSs]

There are 6, 5, 9 and 11 spaces between each text field.

If that is so, does this work for you?
Formula is copied across.

Excel Workbook
	A	B	C	D	E	F
2	7.2.2017 3:24:45 Warning KEPServerEX\Runtime Device 'A_VS20_St050.FX3U' has been auto-demoted.*	7.2.2017	3:24:45	Warning	KEPServerEX\Runtime	Device 'A_VS20_St050.FX3U' has been auto-demoted.*
Extract Parts

 

[Pr0metheus]

Peter_SSs,

it's almost perfect, but some logs are truncated.
this log

7.2.2017 5:29:43 Information KEPServerEX\Configuration Opening project C:\Shared\OPC\Uni14_Daimler_2017_01_18_r00.opf

<tbody>
</tbody>

gives truncated last column:

Opening project C:\Shared\OPC\Uni14_Daimler_2017

 

[Peter_SSs]

.. some logs are truncated.

In the B2 formula change each "100" to "1000" and change the "99" to "999" then copy across and down again.

 

[Pr0metheus]

Extract Parts

*	A	B	C	D	E	F
2	7.2.2017 5:29:43 Information KEPServerEX\Configuration Opening project C:\Shared\OPC\Uni14_Daimler_2017_01_18_r00.opf <colgroup><col></colgroup><tbody> </tbody>	7.2.2017 <colgroup><col width="67"></colgroup><tbody> </tbody>	5:29:43 <colgroup><col width="63"></colgroup><tbody> </tbody>	Information <colgroup><col width="78"></colgroup><tbody> </tbody>	KEPServerEX\Configuration <colgroup><col width="179"></colgroup><tbody> </tbody>	Opening project C:\Shared\OPC\Uni14_Daimler_2017 <colgroup><col width="330"></colgroup><tbody> </tbody>

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:785px;"><col style="width:69px;"><col style="width:62px;"><col style="width:67px;"><col style="width:153px;"><col style="width:348px;"></colgroup><tbody>
</tbody>