Return string of a cell with 3 sections

[rosiemac]

I have cell contents that are like this:

AX/RA/CMB

The format always has 2 x "/" with letters to the left, middle, and right of the "/" , however the number of letters in the text bit varies. Some are 2 digits, some 3 and some more.

e.g. AB/CD/EF or ABC/DEF/GHI or AB/CDE/FG

I would like to use VBA to change the current cell contents so that the cell displays the middle letters, that is the bit in between the "/" and "/"

Hopefully you can help!

 

[stanleydgromjr]

rosiemac,

Excel Workbook
	A	B
1	AX/RA/CMB	RA
2	AB/CD/EF	CD
3	ABC/DEF/GHI	DEF
4	AB/CDE/FG	CDE
Sheet1

The formula in cell B1 (copied down):
=MID(A1,FIND("/",A1,1)+1,FIND("/",A1,FIND("/",A1,1)+1)-FIND("/",A1,1)-1)


Have a great day,
Stan

 

[stanleydgromjr]

rosiemac,

Or, a Function:

Excel Workbook
	A	B	C
1	AX/RA/CMB	RA	RA
2	AB/CD/EF	CD	CD
3	ABC/DEF/GHI	DEF	DEF
4	AB/CDE/FG	CDE	CDE
Sheet1

The Function in cell C1 (copied down):
=ExtractElement(A1,2,"/")



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Function ExtractElement(str, n, sepChar)
'
' Returns the nth element from a string, using a specified separator character
' If A1 contained  546-339-909-944
' =ExtractElement(A1,3,"-")
' =ExtractElement("546-339-909-944",3,"-")
' These formulae return 909, the third element in the string (which uses a "-" as the delimiter).
'
  Dim x As Variant
  x = Split(str, sepChar)
  If n > 0 And n - 1 <= UBound(x) Then
    ExtractElement = x(n - 1)
  Else
    ExtractElement = ""
  End If
End Function

Have a great day,
Stan

 

[rosiemac]

AWESOME! Thank you so much for this. I knew about left and right functions, but I didn't know there was a MID function!