-1

I have a set of strings:

Building_Level_SpecificNumber Name

B_01_16 Wachschutz  

and need to extract each part. Here I need = 16. Here is code I manage to get:

  1. Building: =IF(C10>0,LEFT(C10,FIND("_",C10)-1),0)

  2. Level: =IF(C10>0,LEFT(MID(C10,FIND("_",C10)+1,256),FIND("_",MID(C10,FIND("_",C10)+1,256))-1),0)

  1. Specific number: - not solved yet
  1. Name: =RIGHT(C10,LEN(C10)-SEARCH(" ",C10))

see question on attached image

Michal
  • 19
  • 3
  • 14
  • See [here](http://stackoverflow.com/questions/28582624/extracting-string-separately-from-excel), [here](http://stackoverflow.com/a/19772389/680068) and [here](http://stackoverflow.com/a/31615838/680068). – zx8754 Oct 13 '15 at 08:50

1 Answers1

0

If the number is always two digits you can use this simple formula looking for the second underscore:

=MID(A1, SEARCH("_", A1, SEARCH("_", A1) + 1) + 1, 2)

Otherwise you need to look for the distance between the first space and the second underscore as well:

=MID(A1, SEARCH("_", A1, SEARCH("_", A1) + 1) + 1, SEARCH(" ", A1) - SEARCH("_", A1, SEARCH("_", A1) + 1) - 1)
Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37