Home / Functions / Extract Text from String – using MID Function

Extract Text from String – using MID Function

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

About Excel Help Desk

Check Also

Calculate Number of Days in another Date Range

A custom function has been developed to calculate the Number of Days that one Date Range is within another. This can be very useful when calculating the overlapping periods.

One comment

  1. Thank you so much, Help Desk Team. You are doing a wonderful job.
    The first half of the job that i did on my own took to long and the second half after your solution was done in a jiffy.

    I am so glad i found this site.
    Thanks again.
    Regards