Hi all,

I need to run a loop search where I find a cell in a particular column
that begins with the text, "**Item". I can do that. But when I find it,
there will be a text string of varying length inside that is bordered
by quotes, and I need to extract that string to a variable. The cell
contents will look something like this:

**Item "app04:/C/" for client "app04"

The two strings in quotes are what I need to extract. The structure of
these cells will always be the same, like:

**Item "something" for client "something"

Can anyone help? I've hit a a pothole in my brain. :)

Thanks,

Bob

Use the split command

Demo'd from the immediate window:

? sStr
**Item "app04:/C/" for client "app04"
v = split(sStr,"""")
? v(0)
**Item
? v(1)
app04:/C/
? v(2)
for client
? v(3)
app04

--
Regards,
Tom Ogilvy


"indiana1138" wrote in message
oups.com...
Hi all,

I need to run a loop search where I find a cell in a particular column
that begins with the text, "**Item". I can do that. But when I find it,
there will be a text string of varying length inside that is bordered
by quotes, and I need to extract that string to a variable. The cell
contents will look something like this:

**Item "app04:/C/" for client "app04"

The two strings in quotes are what I need to extract. The structure of
these cells will always be the same, like:

**Item "something" for client "something"

Can anyone help? I've hit a a pothole in my brain. :)

Thanks,

Bob

Hi indiana1138,

Can anyone help? I've hit a a pothole in my brain. :)

correct ;)

loop through the string with the instr-function.

first you could cut off the leading text and the last " (mid-function
and len-function), then replace "for client". this will leave you with

app04:/C/""app04

with the instr-function you'd find the first " so you can use the
left-function and the right function on that string.

Sub test()
txt = Cells(1, 1).Value
Debug.Print txt
txt = Mid(txt, 9, Len(txt) - 9)
Debug.Print txt
txt = Replace(txt, " for client ", "")
Debug.Print txt
pos = InStr(1, txt, """")
Debug.Print pos
text1 = Left(txt, pos - 1)
Debug.Print text1
text2 = Right(txt, Len(txt) - pos - 1)
Debug.Print text2
End Sub

easy :)

arno

If you want to do this without using VBA, you can do it with a Filter
and several formula columns.

1. Apply a filter to the range.
2. Click the filter dropdown arrow and select Custom.
3. Select "contains" and enter "~*~*Item". [The tildes are escape
characters to recognize the *'s since "*" is a wildcard chracter.]
4. First formula column:
=RIGHT(CELL_ADDRESS,LEN(CELL_ADDRESS)-FIND("""",CELL_ADDRESS,1))
5 Second formula column:
=LEFT(FIRST_FORMULA_ADDRESS,FIND("""",FIRST_FORMUL A_ADDRESS,1)-1)

Where CELL_ADDRESS is the relative cell address of the cell to be
searched and FIRST_FORMULA_ADDRESS is the relative cell address of the
cell containing the first formula.

If you are SURE the cell contents is always as you described you can write a
function that splits the string on a single quote mark. (You have to use
double-up the quote mark in the delimiter string, i.e. use four quote marks
as shown below)

Dim Tmp() As String
Dim ItemDescription As String
Dim ClientName As String

Tmp = Split(StringFoundInCell, """")
ItemDescription = Tmp(1)
ClientName = Tmp(3)


"indiana1138" wrote:

Hi all,

I need to run a loop search where I find a cell in a particular column
that begins with the text, "**Item". I can do that. But when I find it,
there will be a text string of varying length inside that is bordered
by quotes, and I need to extract that string to a variable. The cell
contents will look something like this:

**Item "app04:/C/" for client "app04"

The two strings in quotes are what I need to extract. The structure of
these cells will always be the same, like:

**Item "something" for client "something"

Can anyone help? I've hit a a pothole in my brain. :)

Thanks,

Bob

Hi,
This assumes the date is in Column C and that there is a header. Start on
the first date, which would be C2, if there is a header. The miles are
assumed to be in Column D. It also assumes you want to total Monday through
Sunday.
Sub Macro1()
StartDateWeekday = Weekday(ActiveCell.Value)
StartAddress = ActiveCell.Address
StartRow = ActiveCell.Row
Do Until ActiveCell.Value = ""
If StartDateWeekday = 1 Then
ActiveCell.Rows("2:2").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & (ActiveCell.Row - StartRow) &
"]C:R[-1]C)"
ActiveCell.Offset(1, -1).Select
StartRow = ActiveCell.Row
StartDateWeekday = Weekday(ActiveCell.Value)
Else
ActiveCell.Offset(1, 0).Select
StartDateWeekday = Weekday(ActiveCell.Value)
End If
Loop
End Sub

Hope this helps.
Thanks,


"indiana1138" wrote:

Hi all,

I need to run a loop search where I find a cell in a particular column
that begins with the text, "**Item". I can do that. But when I find it,
there will be a text string of varying length inside that is bordered
by quotes, and I need to extract that string to a variable. The cell
contents will look something like this:

**Item "app04:/C/" for client "app04"

The two strings in quotes are what I need to extract. The structure of
these cells will always be the same, like:

**Item "something" for client "something"

Can anyone help? I've hit a a pothole in my brain. :)

Thanks,

Bob

Hi,
Start on the Text you want to extract from, hope this helps.
Sub Macro1()
SearchText = ActiveCell.Value
FirstQuote = Application.WorksheetFunction.Find("""", SearchText, 1)
SecondQuote = Application.WorksheetFunction.Find("""", SearchText,
FirstQuote + 1)
ThirdQuote = Application.WorksheetFunction.Find("""", SearchText,
SecondQuote + 1)
FirstItem = Mid(SearchText, FirstQuote + 1, SecondQuote - FirstQuote - 1)
SecondItem = Mid(SearchText, ThirdQuote + 1, Len(SearchText) - 1)
SecondItem = Mid(SecondItem, 1, Len(SecondItem) - 1)
End Sub

Thanks,

"indiana1138" wrote:

Hi all,

I need to run a loop search where I find a cell in a particular column
that begins with the text, "**Item". I can do that. But when I find it,
there will be a text string of varying length inside that is bordered
by quotes, and I need to extract that string to a variable. The cell
contents will look something like this:

**Item "app04:/C/" for client "app04"

The two strings in quotes are what I need to extract. The structure of
these cells will always be the same, like:

**Item "something" for client "something"

Can anyone help? I've hit a a pothole in my brain. :)

Thanks,

Bob