Text to columns and comma delimited text

[flashback]

I have followed a previous thread on this topic
http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/258a92f541abc3b9,
which covers the same issue that I have, except for the type of data
string.
My data looks like this after I do the TextTOColumns function. I have
added a row with headings to better help understand the final layout.
The first entry contains all the required items to match the columns
and the others show the same problem as in the previous tread. This
issue is beyond my knowledge of VBA. Any feedback/help would be
welcome.
Many thanks in advance!!


Company Name, Account#,CCR, Notes,Address, Unit#,City,Postal/ZipCode,
Phone,Fax, Email,LIC#, WorkType

1. ABC INC, 0000015583, MCR1461, ABC ELE, 1365 MyStreet, U 34, MyCity,
L5T 2J5, T: (123)456-7890, F: 123456-7890, E: (e-mail address removed), LIC #:
0007002665, Work Type:
2. ABC INC, 0000015583, 1365 MyStreet, U 34, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: (e-mail address removed), LIC #: 0007002665,
Work Type:
3. ABC INC, 0000015583, 1365 MyStreet, , MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: (e-mail address removed), LIC #: 0007002665,
Work Type:
4. ABC INC, 0000015583, 1365 MyStreet, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: (e-mail address removed),
5. ABC INC, 0000015583, MCR1461, 1365 MyStreet, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, LIC #: 0007002665, Work Type:

 

[Paul Robinson]

Hi
What is the question?
regards
Paul

 

[flashback]

Hi
What is the question?
regards
Paul





- Show quoted text -

Hi Paul,
I did try to apply the existing code to my data, but the criteria are
different.
I played around with the “For Each Cell In…” and the “If “statement
sections of the code to no avail.
I cannot figure out how to calculate/isolate/compare the values to be
able to determine where to add the extra commas when needed. When I
add the commas manually the TextToColumns works great.
The columns sequence is:
A Company Name -Always present
B- Account# -Always present
C- CCR -NOT always present, text field
D- Notes -NOT always present, text field
E- Address -Always present, 99.99% of the time starts with a number
F- Unit# -NOT always present, when present it starts with the letter
“U”
G- City -Always present
H- Postal/Zip Code -Always present, this can be US or CA formats
I- Phone -Always present, field starts with “T:”
J- Fax -Always present, field starts with “F:”
K- Email -NOT always present, field starts with “E:” and has”@”
L- LIC# -NOT always present, field starts with ”LIC#:”
M- Work Type -NOT always present, field starts with ”Work Type:”

From my analysis columns “C”,”D”,”F” and “K” are the ones that we need
to create the extra commas when they are missing.
I had added the sample data to the posting to show the variation from
row to row.
I hope this helps you, better understand my problem.
Thank for your time!!!
Regards
Joe Silva

 

[killer six]

ok going to reexplain here.

As stated we have the above information that needs sorting.
What we have right now is this. We are able to take the string and put
individual pieces of data within that string into individual columns.
But right now our code just takes a string, looks at the first bit of
data and sticks it into column A, 2nd bit of data into column B, 3rd
bit into column C....... you get the idea.

Now the problem is the order in which the data is in the string is not
exactly the same each time. We would appreciate it if someone would
explain how we can sort through the date so things like the contacts
email doesn't end up in fax column, it goes into the email column (as
an example).

As stated the columns that we need filling are:
A Company Name -Always present and 1st
B- Account# -Always present and 2nd
C- CCR -NOT always present, text field
D- Notes -NOT always present, text field
E- Address -Always present, 99.99% of the time starts
with a number
F- Unit# -NOT always present, when present it
starts with the letter
“U”
G- City -Always present
H- Postal/Zip Code -Always present, this can be US or CA formats
I- Phone -Always present, field starts with
“T:”
J- Fax -Always present, field starts with
“F:”
K- Email -NOT always present, field starts with
“E:” and has”@”
L- LIC# -NOT always present, field starts with
”LIC#:”
M- Work Type -NOT always present, field starts with
”Work Type:”

and example date is above.
I'll get the code we have so far later, can't atm.
Thanks!

 

[Pete_UK]

The thread that you refered to in your original post was concerned
with trying to insert commas at the appropriate place to account for
missing fields. The examples there were fairly straightforward and so
it was relatively easy to recognise fields (and therefore those that
were missing). I would suggest that yours is a somewhat more complex
case, so perhaps a different approach might be more appropriate.

In an ideal case you would have a record like this:

a,b,c,d,e,f,g,h,i,j,k,l,m

but you state that c, d, f, k, l and/or m could be missing. If you
parse the block of data using Text-to-columns you might have something
like this:

a | b | c | d | e | f | g | h | i | j | k | l | m
a | b | d | e | f | g | h | i | j | k | l | m
a | b | c | d | e | f | g | h | i | j | l | m
a | b | c | e | g | h | i | j | k | l | m
a | b | c | d | e | f | g | h | i | j | k | m
a | b | c | e | f | g | h | i | j | l
a | b | c | d | e | g | h | i | j | k | l | m
a | b | c | e | f | g | h | i | j | k | m
a | b | c | d | e | f | g | h | i | j | k | l | m

I'm not sure if this will line up correctly, but I hope you get the
idea.

An algorithm that might work is to start with the rightmost column (m)
and if the cell is empty then there is one (or more) missing cell(s)
somewhere on that row. So, scan adjacent cells on that row looking for
a pattern that matches the cells that you know about (eg can you find
an E: and an @), and insert blank cells as appropriate pushing the
other cells to the right.

I'm not up to putting such a macro together, but I just thought a
different angle on it might prompt someone else.

Hope this helps.

Pete

 

[Paul Robinson]

Hi
1. Where is the data coming from before you see it as a string? Can
you get at it from there??

2. The problem is still not well described enough to be coded up:
(a) C and D (and G?) in your list are text. How can they be
differentiated from each other? They will both have to not start with
U or they cannot be differentiated from F. Is that the case?
(b) E begins with a number. Does nothing else begin with a number (C,
D G and H specifically)?
(c) Is there anything in H to differentiate it from C, D, E, F and G?
What are US and CA formats by the way?

Presumably this string is being lifted from a database or webpage in
some way - if you can get at this source it would simplify things!

regards
Paul