Thread: Parse string into 3 parts using vba

hi, welchs101, you'd better posted sample workbook showing original data and result you need to obtain

i am enclosing an example of what i want. if you need clarification on anything just let me know. thanks again.

please check attachment, run code "test", result is on Sheet3

wow.........that was fast. I am looking at your code now.....have some questions but i will try to ask them later once i have fully reviewed the code. thanks.

i am looking up some of your code and i am having trouble finding good explanations for the following..........was wondering if you could help me figure these out.

x(i, 2) = Trim(Split(x(i, 1), "' canceled")(0))
x(i, 3) = Trim(Replace(Split(Replace(x(i, 1), x(i, 2), ""), "(")(1), ")", ""))

So, i am guessing that the first one "removes" canceled from the string.........but not sure what the "(0)" is doing?

when split function is used the first element of the returned array of strings is zero
So if we apply this function to "zero, one, two" string, element (0) of split with delimiter "," will give you "zero". See VB help for Split function.
Replace method obiously replaces some old symbol(s) to new once ("(" to "" and ")" to "").
Trim function deletes any extra spaces.

got pulled away from this topic but now i am back on it again.

I had a question about the code you posted:

x = .Range(.[a2], .Cells(Rows.Count, "a").End(xlUp).Offset(, 2))

can you explain this a little.

I know your making an array assignment but i am having a hard time understanding what is exactly getting assigned.

define area in A column and offset to the right by 2 columns

I am still going through some of the code........i think i can learn some things i dont already know.

Some of my requirements have changed. i no longer need to parse the string into 3 components as previously stated but rather two .

I want to parse the string such that
String1: This stuff
String2: AAAAAAAAAAAAABBBBBBBBBB (229Dec2009) fkjdfldjkflskjflaskfjlsjdflasjlfjslfjksjflkasjdflkj (+/-7 days) jdlfkjslfjlsdfjk (14Oct2009). Please clarify.' canceled

Basically, now i need to parse the string into two segments.

I can parse the string into two segment using what i have learned here but i cant seem to get the "canceled" part intot the first string.

Any ideas?

in an attempt to understand the code and satisfy my current requirements i am have this code but i am getting a type mismatch run time error .......cant figure out why

Dim junk2 As Variant
junk1 = "AAAAAAAAAAAAABBBBBBBBBB (229Dec2009) fkjdfldjkflskjflaskfjlsjdflasjlfjslfjksjflkasjdflkj (+/-7 days) jdlfkjslfjlsdfjk (14Oct2009). Please clarify.' canceled (This stuff)."
junk2 = Split(junk1, "' canceled")(0)
MsgBox (junk2 & "' canceled")
MsgBox (junk2(1, 1))

error occurs on the "MsgBox (junk2(1, 1))" part of the code....i think

which line makes junk2 a single string?

is it this?

junk2 = Split(junk1, "' canceled")(0)

what does this do exactly?

Is junk2 still an array even if i use the "(0)"

Also, your code works.........thanks.

just trying to understand why.

also what if the "(0)" had been a "(1)" what would this do or mean?