CODEWITHSUNDEEP
excel
Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

Excel formula to split text

I have an Excel sheet like as per the below image.

enter image description here

From this Excel sheet I want to extract data like

enter image description here

I used Excel formula like LEFT(D3,FIND("-",D3)-1). I also know SUBSTITUTE function to remove specific data like SUBSTITUTE(F3,".jar","").

I am not able to implement all of this at a time as I am completely new to do all this in Excel. Is there any way to do this?

Please help me. Thanks in advance

Upvotes: 0

Views: 361

Answers (2)

Xlsx
Xlsx

Reputation: 175

You;re right on the mark with Left & Substitute. Just add on a bit of creativity. If we can assume that version is displayed after the final "-" and extension is after the final ".", we use use place holder characters to track the final "-" & "." of each File Detail.

Assume Assuming FileDetails are in Cell A2, you can input the below formula for FileName & Version in 

Cell B2:

=LEFT(A2,SEARCH("!@#@!",SUBSTITUTE(A2,"-","!@#@!",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1)

Cell C2:

=MID(A2,SEARCH("!@#@!",SUBSTITUTE(A2,"-","!@#@!",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1,SEARCH("#@!",SUBSTITUTE(A2,".","#@!",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-(SEARCH("!@#@!",SUBSTITUTE(A2,"-","!@#@!",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1))

Placeholder for final "-": !@#@!

Placeholder for final ".": #@!

Upvotes: 1

vels4j
vels4j

Reputation: 11298

Try this

+---------------------+-----------------------------------------------------+-----------------+-------------------------------+--+ | File Details | Find | Artifact name | version | | +---------------------+-----------------------------------------------------+-----------------+-------------------------------+--+ | activation-1.1.jar | =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")) | =MID(A2,1,B2-2) | =MID(A2,B2,LEN(A2)-LEN(C2)-5) | | +---------------------+-----------------------------------------------------+-----------------+-------------------------------+--+

Upvotes: 0

Related Questions