Formula to Extract Text (instead of using Text to Columns)

[seenai]

Hi,

I need an excel formula to extract the text between characters "_" (underscore)

Eg in Cell A1 : IN18059850_Plant_T0_1218_New Failure

I need Column B1, C1, D1, E1, F1 to extract the text between '_'

Help me to get the data.

 

[sandy666]

with PowerQuery (Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"})
in
    Split[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2[/td][td=bgcolor:#70AD47]Column1.3[/td][td=bgcolor:#70AD47]Column1.4[/td][td=bgcolor:#70AD47]Column1.5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]IN18059850_Plant_T0_1218_New Failure[/td][td][/td][td=bgcolor:#E2EFDA]IN18059850[/td][td=bgcolor:#E2EFDA]Plant[/td][td=bgcolor:#E2EFDA]T0[/td][td=bgcolor:#E2EFDA]1218[/td][td=bgcolor:#E2EFDA]New Failure[/td][/tr]
[/table]

 

[Eric W]

Try:

	A	B	C	D	E	F
IN18059850_Plant_T0_1218_New Failure	IN18059850	Plant	T0	New Failure

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1218[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),COLUMNS($B1:B1)*999-998,999))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Put the following formula in B1 and copy to the right

=TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",99)),IF(COLUMN()=2,1,99*(COLUMN()-2)),50))

 

[seenai]

hI,

Thank you very much for your solution.

B.Srinivasa Rao

Try:

	A	B	C	D	E	F
IN18059850_Plant_T0_1218_New Failure	IN18059850	Plant	T0	New Failure

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1218[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),COLUMNS($B1:B1)*999-998,999))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Happy to help!