0

I need to extract a string between two other strings where the pattern is almost always the same (it has to be done with a formula and not VBA sadly):

Data structure:

Data                                                      Extract Country
12345678 111 Test Test 132433 172282 111 USA 111 ABCD         USA
123456 111 Test Test 132433 172282 111 GER 111 AB             GER
1231231 222 Test Test 132433 172282 111 JAP 111 A             JAP

The problem I'm having is the data to extract is always between the 111 USA 111, however there is in the beginning also a string of "111" so I need to find the second occurrence of 111, except in the case of Japan here where I need to find the first occurrence.

How can I do this dynamically with an Excel formula, tried the following which almost works:

MID(A2,FIND(CHAR(3),SUBSTITUTE(A2,"111",CHAR(3),2))+3,4)
MJ95
  • 459
  • 1
  • 6
  • 22

1 Answers1

2

Assuming your country code is always 3 letters long and always followed by " 111" (note the space before the 111) then the following formula should do the trick:

=MID(A1,FIND("@",SUBSTITUTE(A1,"111","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"111","")))/LEN("111")))-4,3)

(the crucial part of this formula taken from @tigeravatar's answer here)

edit 1: Almost forgot to mention: I also assumed that @ did not appear anywhere in your string. if it does, change it for another symbol in the formula

edit 2: Basically, I am using tigeravatar's formula to find the position of the last ocurence of "111", we then substract 4 (" 111" being four characters long) and use that as the starting position for a MID statement that extract three characters from the string

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
  • AFAIK @ does not appear, this works like a charm! Thanks! – MJ95 Jul 04 '19 at 19:45
  • Could you explain a bit why this works? Because we are searching from the right now? – MJ95 Jul 04 '19 at 19:54
  • I edited my question to add a brief explanation. Do follow [the link](https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string) to see how that part of the formula works – cybernetic.nomad Jul 04 '19 at 20:15