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.
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.
'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
If blnInBrackets Or strChar = ")" Then strExtractBrackets = strExtractBrackets + strChar
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