Finding and Extracting info from wrapped text cell

[ebrandt]

We are just beginning to use a new service that manages our vendor data-base and sends out e-mailed invitations to bid on our construction projects.

We can export the vendor data to Excel. Once we open it in Excel we need to be able to select vendors and copy their contact info based on their responses to the invitation and what trade they will be bidding on.

This is the way the program exports the data regarding responses and trades. This information is contained in one cell of the spreadsheet and the text is wrapped.

Declined, 03/21/2011, 03001, Concrete Contractor
Accepted, 03/21/2011, 04720, Cast Stone
Declined, 03/21/2011, 06160, Sheathing
Declined, 03/21/2011, 06400, Architectural Woodwork
Declined, 03/21/2011, 09250, Gypsum Board

How can I quickly find and extract the vendors by who has accepted and by trade....ie, "04720, Cast Stone"?

I previously used filters to do this, but cannot do that with wrapped text.

I think the answer may be use an array formula, but I have no experience with that function.

 

[AMAS]

Which version of excel are you using? I have Office 2010 and it can filter them even though the text is wrapped.

Possible solution: Can you parse the column into multiple columns using a comma as your delimiter?

Possible solution: Create a temporary (maybe hidden) sheet where the parsing occurs and a concantuated list is returned for you somewhere on the original sheet. This will require vba.

Others may have better ideas....

Hope this helps.

AMAS

 

[ebrandt]

Which version of excel are you using? I have Office 2010 and it can filter them even though the text is wrapped.

I am using Excel 2007. It only filters to the first line of text, not the wrapped lines.

Possible solution: Can you parse the column into multiple columns using a comma as your delimiter?

I tried this using Data -Text To Columns, but it gets ugly.

Possible solution: Create a temporary (maybe hidden) sheet where the parsing occurs and a concantuated list is returned for you somewhere on the original sheet. This will require vba.

Others may have better ideas....

Hope this helps.

AMAS

Thanks.

 

[edokhotnik]

Use the Data, Text to Columns, Delimited. Use the comma "," as the delimiter. Works for me with your data sample.

 

[Raja Gopalakrishnan]

Hi Try this code in VBA

I have made certain assumptions

1) The wrapped text will be in the cell "A1" of sheet1
2) There will be an additional sheet available i.e. there should be atleast 2 sheets available to run this code
3) Run this code when the sheet1 is active

Option Explicit
Sub Text_Extraction()
Dim A As Integer
Dim B As Double
Dim C As Double
Dim D As Integer
Dim ix As Integer
Dim Mystring As String
Dim E As Integer
' logic to solve the problem
' find the length of the string
' identify the position of the keywords "Declined" or "Accepted"
' find the length of the string before the keywords
' copy the values in to different sheet
' Created by Raja Gopalakrishnan
' Time Start 00:15
' Time end 01.08
A = 1
C = 1

Do While C < Len(Cells(1, 1))
B = InStr(C, Cells(1, 1), "Declined,")
If B <> 0 Then
Cells(A, 2).Value = B
C = B + 1
A = A + 1
Else
C = 1
Do While C < Len(Cells(1, 1))
B = InStr(C, Cells(1, 1), "Accepted,")
If B <> 0 Then
Cells(A, 2).Value = B
C = B + 1
A = A + 1
Else
GoTo Land_1
End If
Loop
End If
Loop

Land_1:

Cells(A, 2).Value = Len(Cells(1, 1)) + 1
ix = Worksheets(1).UsedRange.Rows.Count
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B1", "B" & ix)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
D = 1
Mystring = Worksheets(1).Cells(1, 1).Value
Do While D <= ix
If Worksheets(1).Cells(D + 1, 2).Value <> 0 Then
E = Worksheets(1).Cells(D + 1, 2).Value - Worksheets(1).Cells(D, 2).Value
Worksheets(2).Cells(D, 1).Value = Mid(Mystring, Worksheets(1).Cells(D, 2).Value, E)
D = D + 1
Else
Exit Sub
End If
Loop
End Sub


Any modifications and suggestions for improving the code is welcome...

 

[ebrandt]

Thanks for the code Raj........I will try this tomorrow.....out of time today.

 

[ebrandt]

I tried this Raja and the result was just the number 99 in cell b1.

 

[AMAS]

Hi again,

Sorry it took me so long to figure this out, but here goes nothing...

Using your examples, and assuming that everything is starting at A1 then put this formula in B1 and drag down:

=IF(LEFT(A1,FIND(", ",A1)-1)="Accepted",RIGHT(A1,LEN(A1)-FIND(", ",A1,FIND(", ",A1)+2)),"Declined")

The returned value is "Declined" except for B2 which will give you (I hope) the needed answer: 04720, Cast Stone

Let me know how it runs.

AMAS

 

[Raja Gopalakrishnan]

I tried this Raja and the result was just the number 99 in cell b1.

Hi

See Sheet2 You should find the extraced data

Also one more assumption is I took "Declined," and "Accepted," If there is no comma just in the code remove the same from code.

-- Raja

 

[Raja Gopalakrishnan]

I tried this Raja and the result was just the number 99 in cell b1.

Also is it possible to paste the data for me to cross check