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

20 pts.
Tags:
Excel formulas
Excel functions
Excel macros
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
ASKED: May 2, 2008  8:51 AM
UPDATED: May 6, 2008  1:36 PM

Answer Wiki

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

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

<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
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
</pre>

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following