Question

Retreiving data from within square brackets, there may be more than one in each cell (spreadsheet owner refuses to change it grrrr)

Excel formulas, Excel functions, Excel macros, Excel help, Microsoft Excel

I have a spreadsheet with a column containing data in the following format

Column G
Row 1 [1T40131A] [GB50131A] [GB50131B] [GB50131C] [1T50131D]
Row 2 [1T400133]
Row 3 [303640]*VF
Row 4 [240700]*RN

I would like to create a UDF that will retrieve the data within the brackets and create a column on another worksheet with them all on a seperate row i.e.

Column A
Row 1 1T40131A
Row 2 GB50131A
Row 3 GB50131B
Row 4 GB50131C
Row 5 1T50131D
Row 6 1T400133
Row 7 303640
Row 8 240700

Is this possible with a UDF and if so could anyone give me some idea how to get started, Im a bit of a cut n paster normally but this one is beyond my google browsing abilities :)

any help would be much appreciated

Hi,
What you mean "UDF"? To me, UDF is a database concept, an "User defined Function".
I know how to do this using an Excel VBA macro, like the one at the bottom of this posting.
--
Juan

Option Explicit
 
 Sub main()
     GetFromSquareBrackets Sheets("sheet1"), Sheets("sheet2")
 End Sub
 
 Sub GetFromSquareBrackets(sourceSheet, targetSheet)
 
     Dim sourceCol As Long, sourceRow As Long
     sourceCol = 1
     sourceRow = 0
     Dim targetCol As Long, targetRow As Long
     targetCol = 1
     targetRow = 0
     
     Dim iRow As Long
     iRow = 1
     Do ' for each input row containing data
         If sourceSheet.Cells(iRow, sourceCol) = Empty Then
             ' empty cell: loop no more
             Exit Do
         Else
             Dim inputData, newValues
             inputData = Trim(CStr(sourceSheet.Cells(iRow, sourceCol)))
             ' split cell content on the separating blank space
             newValues = Split(inputData, " ")
             ' separated element(s) have all the brackets, clean up
             Dim theCode
             For Each theCode In newValues
                 ' get rid of the opening bracket
                 theCode = Replace(Trim(theCode), "[", "")
                 ' find the closing bracket and trim from there
                 Dim n
                 n = InStr(1, theCode, ">")
                 If n > 0 Then theCode = Left(theCode, n - 1)
                 ' add the code in next row of the target sheet
                 If Len(Trim(theCode)) > 0 Then
                     targetRow = targetRow + 1
                     targetSheet.Cells(targetRow, targetCol) = "'" & theCode
                 End If
             Next
         End If
         ' move to next input row
         iRow = iRow + 1
     Loop
 End Sub