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

20 pts.
Microsoft Excel
Microsoft Excel formulas
microsoft excel functions
Microsoft Excel macros
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.


<pre>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
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
End If
‘ move to next input row
iRow = iRow + 1
End Sub

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Harleq
    That worked great, thanks for the help. yeah I did mean user defined function as this was what I used before to make a countif that went accross sheets, but a macro is fine for this as it didnt need to be updated live, can just be run every now n then. Thanks for the help dude
    20 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: