Extracting info between 2 symbols in one cell

[ELFIJKA]

I'm stuck and need Excel GURU help!

I have thousands of stock codes I need to be able to extract certain info from code. Codes can be different in lenght, so Left and Right formula doesn't work...

Stock code format is as follows: AA/BBB/123/PP/XY02 - most important here are "/" - they are used as dividers of different type of info about the product. The length of characters in between can vary.

I need to extract the "123" - it is always between 2nd and 3rd "/".

Is there is an easy solution to this?

Thank you so much for your help in advance

[bebo021999]

Working with cell A1:

Please Login or Register  to view this content.

give "123" in text format
Formula+0 to get 123 as value

[Punnam]

Please Login or Register  to view this content.

[ELFIJKA]

Hi All,

Here I'm back now looking for more help. bebo021999 suggestion to use =TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",1000)),2000,1000)) works brilliantly! but now I need to extract the 4th part of code (between 3rd and 4th "/") eg. code AA/BBB/123/PP/XY02 and I need to extract "PP" from it. How to alternate the formula to get this answer?

Thank you so much!

[AliGW]

Third segment = 3000:

=TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",1000)),3000,1000))

[ELFIJKA]

AliGW Brilliant! Thank you so much!

[MrShorty]

I will throw this into the mix -- just in case you are not limited to Excel for this. I think Google Sheets SPLIT() function makes easy work of problems like this. =SPLIT(A1,"/") entered in a cell (Sheets has a similar Spill feature to the newest versions of Excel) will separate each part of the text string into a separate cell. Or you can nest that inside of an INDEX() function to get a single value =INDEX(SPLIT(A1,"/"),3) will extract the 3rd element from the text string/array. =INDEX(SPLIT(A1,"/"),B1) will allow you to enter 3 or 4 or whatever into B1 and the formula will output the corresponding element from the text/array.

Note that the SPLIT() function is currently only supported by Google Sheets.

Also note that this function is very similar to the VBA Split() function, and I have seen some propose a VBA UDF (in an add-in) that makes this function available to Excel.

These TRIM(MID(SUBSTITUTE(...)...) type function also work, but I find them less easily understood, which makes them more difficult to edit or modify when one needs something different. The SPLIT() function that converts a delimited text string into an array makes more sense to me, so I find it easier to work with.

For whatever little it is worth.

[AliGW]

AliGW Brilliant! Thank you so much!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[ELFIJKA]

I completely agree and I don't understand why Microsoft still hasn't included SPLIT formula as standard... but for now I'll keep using TRIM(MID(SUBSTITUTE(...)...) as we are in MS Office and I need to get it working...

Thanks a lot for sharing!