Extract text between double quotes in variable length strings

[3gswish]

Hi everyone,

A bit of a challenge with this one... The last couple of hours searching have not resulted in a working solution, so I turn to you.

I have 253 cells in column AO that contain text, containing 1 to 6 chunks of text within "double quotes". Here is an example.

This is text that is "pretty"
This "text" is not "well formed" or well mannered
This "set of words" has been "significantly modified" and is "junk", "junk2" and "junk3"

The length of the text, the length of the quoted chunks, the number of quoted chunks, as well as the location of the quoted chunks within the string are completely variable.

The result I would like to acheive is for each chunk, without it's quotes, to be in the next columns on the same row.

The text is in row AO2:AO254, so chunks should end up in the same row, starting at column AP and higher.

The results would look like

[TABLE="width: 500"]
<tbody>[TR]
[TD]AP[/TD]
[TD]AQ[/TD]
[TD]AR[/TD]
[TD]AS[/TD]
[TD]AT[/TD]
[/TR]
[TR]
[TD]pretty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]text[/TD]
[TD]well formed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]set of words[/TD]
[TD]significantly modified[/TD]
[TD]junk[/TD]
[TD]junk2[/TD]
[TD]junk3[/TD]
[/TR]
</tbody>[/TABLE]



Any and all input would be very greatly appreciated - Thank you, Thank you, Thank you!

 

[konew1]

Here is a formula solution. It uses a lot of helper cells unfortunately. They could be embedded ito single formula for each quote but the resulting formula will be enormous. I have put the results in a panel under the data, only so it will display on the message board. Drag the results over to the data lines and drag them to close the spacing

Excel Workbook
	A	B	C	D	E	F	G	H
2	*	*	1	*	2	*	3	*
3	No of Quotes	*	Start 1	*	find 2	*	find 3	*
4	*	*	*	*	*	*	*	*
5	2	This is text that is "pretty"	22	This is text that is pretty"	28	This is text that is pretty	*	*
6	4	This "text" is not "well formed" or well mannered	6	This text" is not "well formed" or well mannered	10	This text is not "well formed" or well mannered	18	This text is not well formed" or well mannered
7	10	This "set of words" has been "significantly modified" and is "junk", "junk2" and "junk3"	6	This set of words" has been "significantly modified" and is "junk", "junk2" and "junk3"	18	This set of words has been "significantly modified" and is "junk", "junk2" and "junk3"	28	This set of words has been significantly modified" and is "junk", "junk2" and "junk3"
8	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*
10	*	*	*	pretty	*	*	*	*
11	*	*	*	text	*	*	*	well formed
12	*	*	*	set of words	*	*	*	significantly modified
Sheet10

EDIT:
I see the message board didn't show the width. Here is another try of the same sheet.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	*	*	1	*	2	*	3	*	4	*	5	*	6	*	7	*	8	*	9	*	10	*
3	No of Quotes	*	Start 1	*	find 2	*	find 3	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	2	This is text that is "pretty"	22	This is text that is pretty"	28	This is text that is pretty	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	4	This "text" is not "well formed" or well mannered	6	This text" is not "well formed" or well mannered	10	This text is not "well formed" or well mannered	18	This text is not well formed" or well mannered	29	This text is not well formed or well mannered	*	*	*	*	*	*	*	*	*	*	*	*
7	10	This "set of words" has been "significantly modified" and is "junk", "junk2" and "junk3"	6	This set of words" has been "significantly modified" and is "junk", "junk2" and "junk3"	18	This set of words has been "significantly modified" and is "junk", "junk2" and "junk3"	28	This set of words has been significantly modified" and is "junk", "junk2" and "junk3"	50	This set of words has been significantly modified and is "junk", "junk2" and "junk3"	58	This set of words has been significantly modified and is junk", "junk2" and "junk3"	62	This set of words has been significantly modified and is junk, "junk2" and "junk3"	64	This set of words has been significantly modified and is junk, junk2" and "junk3"	69	This set of words has been significantly modified and is junk, junk2 and "junk3"	74	This set of words has been significantly modified and is junk, junk2 and junk3"	79	This set of words has been significantly modified and is junk, junk2 and junk3
8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
10	*	*	*	pretty	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	*	*	*	text	*	*	*	well formed	*	*	*	*	*	*	*	*	*	*	*	*	*	*
12	*	*	*	set of words	*	*	*	significantly modified	*	*	*	junk	*	*	*	junk2	*	*	*	junk3	*	*
Sheet10

 

[Rick Rothstein]

Put this formula in AP2 and copy it across as far as you think you will ever need, then copy all those cells down as far as you thing you will ever need...

=TRIM(MID(SUBSTITUTE($AO2,"""",REPT(" ",999)),2*COLUMNS($AP:AP)*999-998,999))

 

[3gswish]

Hi Konew1,

I started down that road, but did not have the patience you possess!

I was able to derive the first value with this formula, but as you state, further extraction would get to be very large. For each column though, using the previous columns second find +1, and then the rest would be consistent among the rest of the cells I would think.

Thanks for the info!

 

[3gswish]

Ahhh, so elegant...

Thank you so much Rick, it works beautifully.

I was able to finally get the first set done using:

=MID(AO2,(FIND("""",AO2,1)+1),(FIND("""",AO2,(FIND("""",AO2,1)+1)))-(FIND("""",AO2,1)+1))

Each column would have to be unique - which your version solves.

Thanks again!

Put this formula in AP2 and copy it across as far as you think you will ever need, then copy all those cells down as far as you thing you will ever need...

=TRIM(MID(SUBSTITUTE($AO2,"""",REPT(" ",999)),2*COLUMNS($AP:AP)*999-998,999))

 

[b.downey]

Here is a VBA Solution


Just change the BaseDstColNo constant to the number equivalent of "AP"

Option Explicit
Const BaseDstColNo = 10


Sub MainSub()
    Dim wsSrc As Worksheet
    Dim RowNo As Long
    Dim DstColNo As Long
    Dim s As String
    
    Dim StartPos As Integer
    Dim EndPos As Integer
    
    Set wsSrc = ActiveWorkbook.Worksheets(1)
    
    RowNo = 1
    Do While wsSrc.Cells(RowNo, "AO") <> ""
        s = wsSrc.Cells(RowNo, "AO")
        StartPos = 0
        DstColNo = BaseDstColNo
        Do While StartPos < Len(s)
            StartPos = InStr(StartPos + 1, s, Chr(34))
            Debug.Print "---> " & StartPos
            If StartPos < 1 Then Exit Do
                
            StartPos = StartPos + 1
            EndPos = InStr(StartPos + 1, s, Chr(34))
            wsSrc.Cells(RowNo, DstColNo) = Mid(s, StartPos, EndPos - StartPos)
            DstColNo = DstColNo + 1
            StartPos = EndPos
        Loop
        RowNo = RowNo + 1
    Loop
End Sub

 

[Rick Rothstein]

Here is a VBA Solution

Just change the BaseDstColNo constant to the number equivalent of "AP"

I am not sure what the purpose of your code is for... it just seems to print numbers to the Immediate Window (position and length I am thinking). Is that what you intended?

 

[b.downey]

I am not sure what the purpose of your code is for... it just seems to print numbers to the Immediate Window (position and length I am thinking). Is that what you intended?

The code starts at cell "AO1". It separate the value found in this based on the instructions in the original post. (i.e. separated by quotes). It will place the results starting in the column defined by the BaseDstColNo constant. It then will move down column "AO" separating each row per the specs.


The "Debug.Print" statement is for debugging. It can, and should be removed from the code.


Bill

 

[Rick Rothstein]

The code starts at cell "AO1". It separate the value found in this based on the instructions in the original post. (i.e. separated by quotes). It will place the results starting in the column defined by the BaseDstColNo constant. It then will move down column "AO" separating each row per the specs.

The "Debug.Print" statement is for debugging. It can, and should be removed from the code.

BaseSetColNo = 10... oh, so that is where the output went to. I figured you would have specified the column next to the data and when I did not see any output except from the Debug.Print statement, I figured something was wrong with your code. Sorry, I should have read your message and code more carefully. If you are interested, there is a much shorter way to do this with a macro (I moved the BaseDstColNo into the macro instead of leaving it globally available outside of the macro)...

Sub MainSub()
  Dim X As Long, Z As Long, WS As Worksheet, Parts() As String
  Const BaseDstColNo = 42
  Const RowNo As Long = 1
  Set WS = Sheets("Sheet2")
  For X = RowNo To WS.Cells(Rows.Count, "AO").End(xlUp).Row
    Parts = Split(WS.Cells(X, "AO"), """")
    For Z = 1 To UBound(Parts) Step 2
      WS.Cells(X, BaseDstColNo).Offset(, Int(Z / 2)) = Parts(Z)
    Next
  Next
End Sub

 

[b.downey]

Elegant. I like the implementation of the split function and following loop where you are extracting every other index