Extract Text from String – using MID Function

1
158

The ExcelHelpDesk Support team received the following request for help

Problem – Matching Names and Numbers

I need help with a small problem. I have tried to look through the previous entries but couldnt find something similar.

I have a column of data :
1. membranas (f.) mucosas
2. tapa (f.) / capucha (m.) de la aguja
3. picaduras (f.) con agujas

From this i have to take out (f.) or (m.) in a separate column. I am using mid and find function
MID(A2,FIND(“(“,A2),4) , this is giving me the desired result that is (m.) or (f.) in a separate column. My problem is that some cells contain (m.) and (f.) both but this function is only returning the first string. How do i get all (m.) and (f.) in a separate column.

Greatly appreciate any help and if i get no response then also thanks a lot for this wonderful site.

Regards
Jimmy

For this problem we received a sample workbooks. We then used VBA to create a custom function to extract the strings from the starting value and display in a separate column. See below the sample workbook received for this request.

Step 1 – Create a Custom Function

To acheive the desired result the simplest method is to create a custom function that will step through each string and extract the values between brackets into another string. Then to display that string in the result. See below the VBA code that was created for this function.

Function strExtractBrackets(strValue As String) As String
'This function will extract into another string any values in a string that
'contain a Bracket

Dim strChar As String
Dim i As Integer
Dim blnInBrackets As Boolean

blnInBrackets = False
For i = 1 To Len(strValue)
strChar = Mid(strValue, i, 1)
Select Case strChar
Case "(": blnInBrackets = True
Case ")": blnInBrackets = False
End Select
If blnInBrackets Or strChar = ")" Then strExtractBrackets = strExtractBrackets + strChar

Next

End Function

Step 2 – Copy the VBA code to Workbook

Now to use this customer function you need to open the workbook that has the string value that needs to be manipulated and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert > Module. Copy the code from above into the new Module

Step 3 – Use the Custom Function

Back on the worksheet that has the string value that needs the values extracted from we will use the custom function. In an available column next to the string type =strExtractBracket(B2) where B2 is the cell of the starting string. Then drag that formula down for all the values that need to be changed. See the result of the formula on the supplied workbook below

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment