PDA

View Full Version : [SOLVED] Extract data between brackets



parscon
10-02-2014, 03:33 AM
I need a VBA Code that can extract data from column A between brackets () like the below example :

I have these data on column A1 and when run VBA code must give me the below result :

VP,ST,VCAD,X27,(PC3601),PC3601,PC3601_0_1,(TR),(VP),(PS3601)

Result must be in column B1

(PC3601),(TR),(VP),(PS3601)

gmayor
10-02-2014, 04:07 AM
The following will do it using a macro. It should be possible to use a function also, but I'll leave that to the Excel specialists.


Dim LastRow As Long
Dim i As Long
Dim vCell As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow 'Change 1 to 2 if there is a header row
vCell = Split(Cells(i, 1), Chr(44))
Cells(i, 2) = vCell(4) & Chr(44) & _
vCell(7) & Chr(44) & _
vCell(8) & Chr(44) & _
vCell(9)
Next i

parscon
10-02-2014, 04:10 AM
Dear gmayor , Thanks for your help but when run your vba code this error will be appear

Run-time error '9':

Sunscript out of range

I added the Excel file that need this VBA code for it.

Thank you again

gmayor
10-02-2014, 05:04 AM
It doesn't work because your column content bears no relationship to the quoted example. There is no consistency in the column data that would lend itself to such a macro.

How for example does

"CANOPY US"

relate to

"L = 600 MM"

or

"(14014602),L = 35 MM,M8x35,(VOE 955298),(955298),L=35,E PROD,AC: 14256846,L = 35mm: TD 61 GB,L = 35mm,L = 35,L50B,(11993555)"

I think the task is impossible.

lecxe
10-02-2014, 10:50 AM
Hi

You can also try this udf:



Function ExtractData(s As String) As String

ExtractData = Join(Filter(Split(s, ","), "("), ",")
End Function

Kenneth Hobs
10-02-2014, 12:01 PM
lecxe's method works fine. Select column B and change the format to General. Add the macro code to a Module. In B1, =ExtractData(A1), and copy or fill down.

parscon
10-02-2014, 12:51 PM
Thanks, You save me .

lecxe
10-02-2014, 03:47 PM
You're welcome. Thanks for the feedback.