CODEWITHSUNDEEP
excel
user3959104
user3959104

Reputation: 65

How can I search and extract in between slash in Excel formula?

I have a list of rows in excel as below.

Australia Order / 111233 -12213AUS / AUD900 / Ready to dispatched  
UnitedKingdom Order / 187633 -123413UK / USD 800 / goods  
1133 -1013AUS/Australia Order /  AUD450 / Ready to dispatched  
Australia Order /AUD900 / Ready to dispatched / 1873 -1726A

How can I search "-" and extract the value into a new column? Some of the "-' number are not in sequence after the slash.

Expected result:

111233 -12213AUS  
187633 -123413UK  
1133 -1013AUS  
1873 -1726A

Upvotes: 0

Views: 2192

Answers (2)

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

This will work in your case:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("/",A1&"/",FIND("-",A1))-1),"/",REPT(" ",99)),99))
  1. Find index "/" after "-" in string -> extract text from begin to this index
  2. Get last word from this string

enter image description here

Hope it helps!

Upvotes: 2

Jerry
Jerry

Reputation: 71538

There is quite a bit of string manipulation involved, but I guess you can try this:

=TRIM(MID(LEFT(A2,IFERROR(FIND("/",A2,FIND("-",A2))-1,LEN(A2))),IFERROR(FIND("@",SUBSTITUTE(A2,"/","@",LEN(LEFT(A2,IFERROR(FIND("/",A2,FIND("-",A2))-1,LEN(A2))))-LEN(SUBSTITUTE(LEFT(A2,IFERROR(FIND("/",A2,FIND("-",A2))-1,LEN(A2))),"/",""))))+1,1),LEN(A2)))

The formula basically locates the position of the - and gets the position of the previous and next / relative to that of the -.

enter image description here

Alternatively you could use text to columns and use a lookup with a wildcard:

enter image description here

enter image description here

Upvotes: 3

Related Questions