# Return string of a cell with 3 sections

#### rosiemac

##### New Member
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!

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
rosiemac,

Excel Workbook
AB
1AX/RA/CMBRA
2AB/CD/EFCD
3ABC/DEF/GHIDEF
4AB/CDE/FGCDE
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

rosiemac,

Or, a Function:

Excel Workbook
ABC
1AX/RA/CMBRARA
2AB/CD/EFCDCD
3ABC/DEF/GHIDEFDEF
4AB/CDE/FGCDECDE
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).

Code:
``````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

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

Replies
7
Views
501
Replies
3
Views
420
Replies
2
Views
225
Replies
9
Views
335
Replies
1
Views
648

1,196,048
Messages
6,013,093
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back