Split data of cell without loosing separators in results.

[AnkurKatyal]

I have data in Column A with values like following:

Row1= 2e/000/65/rt/67/

Row2= 5e/ert/004/gt/7u/

Row3= 2f/ert/5t/rt/7u/



I want to split data of each cell considering / as seperator. But I dont want / sign removed when output is generated.

Data in column B upto Row 3 should be as:

Row1= 67/

Row2= 7u/

Row3= 7u/

Data in column C upto Row 3 should be as:

Row1= 65/

Row2= 004/

Row3= rt/

 

[Nebu]

Hi:

Find the attached.

Thanks

 

[xlstime]

Hey Ankur,

try below formula without VBA

b5 = =TRIM(MID(SUBSTITUTE("/"&$A5,"/",REPT(" ",164)),164*COLUMN(A3),165))&IF(COLUMN(A1)<=(LEN($A5)-LEN(SUBSTITUTE($A5,"/",""))),"/","")

I have data in Column A with values like following:

Row1= 2e/000/65/rt/67/

Row2= 5e/ert/004/gt/7u/

Row3= 2f/ert/5t/rt/7u/



I want to split data of each cell considering / as seperator. But I dont want / sign removed when output is generated.

Data in column B upto Row 3 should be as:

Row1= 67/

Row2= 7u/

Row3= 7u/

Data in column C upto Row 3 should be as:

Row1= 65/

Row2= 004/

Row3= rt/

ey

 

[bosco_yip]

I2, formula copy across and down :

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&SUBSTITUTE($A2,"/","/ ")," ",REPT(" ",30)),COLUMN(A1)*30),30))

Regards
Bosco

 

[AnkurKatyal]

Thnx All.
I also searched a bit after posting this question to various sites.
What I found is that Flash Fill function too works well when we need to split cells with a specific pattern.

 

[shrivallabha]

Thnx All.
I also searched a bit after posting this question to various sites.
What I found is that Flash Fill function too works well when we need to split cells with a specific pattern.

Flash-fill is available in Excel 2013 and higher versions. Formula solutions will work in all versions.