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]

Column1		Column1.1	Column1.2	Column1.3	Column1.4	Column1.5
IN18059850_Plant_T0_1218_New Failure		IN18059850	Plant	T0	1218	New Failure

 

[Eric W]

Try:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),COLUMNS($B1:B1)*999-998,999)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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
1	IN18059850_Plant_T0_1218_New Failure	IN18059850	Plant	T0	1218	New Failure

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",999)),COLUMNS($B1:B1)*999-998,999)) <tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

Happy to help!