Parse string into 3 parts using vba

[welchs101]

I a column of strings such as below:

Please Login or Register  to view this content.

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

Any ideas?

[watersev]

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

[welchs101]

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

[watersev]

Are there all possible options for strings syntax shown?

[watersev]

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

[welchs101]

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.

[welchs101]

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.

Please Login or Register  to view this content.

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

[watersev]

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.

[welchs101]

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

I had a question about the code you posted:

Please Login or Register  to view this content.

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.

[watersev]

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

[welchs101]

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?

[welchs101]

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

Please Login or Register  to view this content.

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

[DonkeyOte]

In your code junk2 becomes a single string - text prior to delimiter.

Using your specific approach above you would modify to:

Please Login or Register  to view this content.

obviously modify per your own requirements.

[welchs101]

which line makes junk2 a single string?

is it this?

Please Login or Register  to view this content.

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.

[welchs101]

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

[DonkeyOte]

please refer to earlier post in your thread

In short the Split will generate a 1 dimensional 0-based Array
If you subsequently apply a # against the resulting Array eg Split(..)(1) you are explicitly using that item from the 1 dimensional array
(1 being the 2nd item in a 0 based array [0 is first obviously])

[DonkeyOte]

On an aside - when working through code like this - ensure the Locals Window is open and step through the code line by line (F8) - this will help you establish what's going on at run time.

[welchs101]

thanks gusy.

I will try thelocals + F8 option to see how things work.

Also, any idea on how to split the string so that i have the " ' canceled" part into the first string of the split........is the best way just to reassign it using the concat?

also, i have to do this on a column of data.

thanks again.

[welchs101]

i know somone tried to explain this in an earlier part of the thread but i think there is more to this than what they said......

Please Login or Register  to view this content.

Is this defining some sort of array? any links to try and understand this would be helpful.

[welchs101]

ok, so what i am confused about is this.

The previous code i mentioned may just define an area..............but in an earlier post someone used x in the following manner which i think is a 2 dimensional array.......right

Please Login or Register  to view this content.

[welchs101]

could someone explain this code in detail.......i just really confused as to "how" and "what" its doing exactly.

Please Login or Register  to view this content.

[Leith Ross]

Hello Welchs101,

The snippet is part of a With ... End construct. It looks more like this ...

Please Login or Register  to view this content.

The period indicates that the object following it is part of the object following the With statement. In this case that object is the left most worksheet in the workbook - "original_data".

The variable "x" will be assigned a value from the defined range on "original_data". A Range object takes 2 arguments: a starting range and an end range. The range starts at cell "A2". The end range is calculated by counting from the last cell of the worksheet, given by Rows.Count, and moving up column "A" until a non empty cell is found. Lastly, the value from the cell 2 columns to the right ,.Offset(0, 2), of the end cell is assigned to "x".

[welchs101]

thanks. this is the same thing the other person had said but explained in more detail. thanks.

I think here is where i am getting confused.

Please Login or Register  to view this content.

I have strings i am trying to parse in cells a2 through a8. If i print to the screen using "msgbo(x(1,1))", "msgbox(x(2,1))", etc..........

I can actually see all the entries in col A. So this is why i was asking about the statement.

So , why does "x" appear to be a 2 dimensional array?

[welchs101]

i am enclosing a picture of the "locals" window......of what the value of X is .......from the window it appears X is a multidemsional array.........how did this happen?

not saying this is bad again just trying to understand some code someone posted on here that works but dont want to use until i understand it.

[welchs101]

ok i just found this and this explains why x is variant

Please Login or Register  to view this content.

i was thinking "x" was defined as a long...........

but i still dont understand why "x" is 3 dimensional

anyone know?

[watersev]

Please Login or Register  to view this content.

because x equals to range values that consists of three columns A,B,C (A column offset 2 columns to the right will give column C, so the range is from A2 to last row with value in C column)

[broro183]

hi everyone,

I was just wandering past and am not intending on taking an ongoing active part in the thread but I have a couple of thoughts to share.

Welchs101,
I applaud your desire to understand the code, asking questions now is likely to accelerate your rate of learning dramatically. There are numerous ways to learn what the code is doing and some have already been mentioned:
- [F8] in conjunction with the Locals Window
- make the [F1] key your friend!
You can double click on keywords to select them & then press [F1] to bring up any relevant VBA Help File. Also, you can search for explanations in the Helpfiles by typing into the searchbox.
- msgboxes, or the less interruptive approach is to use the Immediate Pane in the VBE ([ctrl + g]). You can use this directly by typing (into the Immediate Pane of the VBE) a question mark followed by a code phrase that can give a value, for example,

Please Login or Register  to view this content.

or alternatively, within your code you can use "debug.print" statement within your code to return a value to the Immediate Pane as the code is running eg

Please Login or Register  to view this content.

- then ask questions for things you still don't understand! ;-)

*I have used "application.goto" in case the desired worksheet is not active. The use of these final 2 approaches are good for testing but can/should be removed from final code where possible, because it is very rare that you need to "select" an object/range before performing an action on it in code.

I haven't looked at any of the attached files, only the code snippets on screen so I don't know exactly what is being done by the code However, you may find some useful discussion about the use of variant arrays in this link, http://www.dailydoseofexcel.com/arch...nge-using-vba/, and it may even be the underlying reason for the use of variant arrays to be offered as a solution.

Watersev,
Nice work so far
btw, check out Chip's "optimise vba" page for discussion about the use of "[a1]" in identifying a range.

hth
Rob

[welchs101]

thanks for the info. i just discovered the locals pane through this thread as a matter of fact. Its neat and helpful. And thanks for the debug.print info.....also very helpful since i could not always get the statement i wanted to show up in the locals pane.

[welchs101]

ok couple things:

1) I think i figured out where the 3 dimensional array is coming from

Please Login or Register  to view this content.

The above gives a 3 dimensional array



If i change the 2 to a 1 i get a 2 dimensional array (very cool).

Please Login or Register  to view this content.

BUt i am having trouble getting the data to show up in the locals window for some reason. I tried the debug print and i get a mismatch error. what i have to do to get it to sho wup in the locals window is have the program stop at the statement following this

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

and then it will show up in the locals window.

[broro183]

hi Welchs101,

I think the thread is evolving radically from a specific "parsing a string" question into a general "many to one" training thread. It seems to me that Watersev's solution answers your question.

I recommend you read the following links to fill some of the gaps in your "foundation" knowledge. The code you have been given is not beginner's code & after reading the links you won't be as much of a beginner so it should become easier to understand...

- All links in the "VBA - For Beginners" section of http://www.excelforum.com/excel-gene...additions.html
- I've only seen this today & Neopa wrote it for MS Access, but at a glance, I think most of it is suitable for excel too: http://bytes.com/topic/access/insigh...-debugging-vba
Here are two more threads that you can learn from too. I acknowledge that some posts may be "over your head" at this stage, but there will be a fair few that will help you now. Then, with a bit more reading & a lot of experimenting, the more complex details will become meaningful for you:
http://www.excelforum.com/the-water-...hers-wont.html
http://www.excelforum.com/excel-prog...id-in-vba.html


re "3 dimensional array":
- Can you please modify your profile to include which version of excel are you using?
The reason I ask is that different solutions may be need for different versions and the specific Help files may differ between versions. However, the VBE Help files of any version are likely to give you some useful information if you look up "Range" & "Offset". Based on the explanations you should be able to understand more of the "why" your code is affected (as explained in Watersev's response) when you change a "2" to a "1" in the section of code you are looking at.
- This is still a two dimensional array not 3 dimensional, and is shown in your picture as "x(1,1)" where each digit represents a dimension. The location of the first "1" can be considered the placeholder that identifies the row, while the location of the second "1" can be considered the placeholder that identifies the column. A three dimensional array would be presented as "x(1,1,1)". I won't try & explain in any more detail because I may end up using incorrect terminology

re the Locals window:
- This is the "Local variables" window so if you have not declared something as a variable (ie "dim'ed" it & then assigned a value to it), it won't be shown in the window.

re the "debug.print" & the Immediate Pane/Window:
Debug.print can only be used to "print" a string (text or numeric) to the Immediate pane, it can not be used to return an Object such as a range. This is why my examples (I hope!?) included a ".address" on the end of the line of code. By doing this, I was asking the computer to "print" a string, stating the address of the Range Object, into the Immediate Pane.

hth
Rob

[broro183]

howdy,

Here's another useful link on Chip's site (http://www.cpearson.com/excel/Debug.htm) which I've just seen in another thread. btw, Chip's website is full of useful stuff so it may be useful for you to read other pages on his site too.

hth
Rob

[welchs101]

I agree, its kinda gotten off topic

sorry about that ......but my all my questions were centered around trying to figure out this really neat code.

thanks to all who responded.

i really appreciate all the feedback and instructions.

[welchs101]

Just wanted to follow up on this post.

Watersev, thanks...........your code really helped out. Learned alot from it. Hoping to apply what i learned to other things.

I am enclosing the file..........its not the polished end product but its what i came up with based on watersev's code. Modified it slightly to fit my ever changing requirements.

thanks to all who responded and helped me.