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 a
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!


On Sep 8, 9:12*am, Paul Robinson wrote:
Hi
What is the question?
regards
Paul

On Sep 8, 6:21*am, flashback wrote:

I have followed a previous thread on this topichttp://groups.google.com/group/microsoft.public.excel.programming/bro...,
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: , LIC #:
0007002665, Work Type:
2. * * *ABC INC, 0000015583, 1365 MyStreet, U 34, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: , LIC #: 0007002665,
Work Type:
3. * * *ABC INC, 0000015583, 1365 MyStreet, , MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: , LIC #: 0007002665,
Work Type:
4. * * *ABC INC, 0000015583, 1365 MyStreet, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: ,
5. * * *ABC INC, 0000015583, MCR1461, 1365 MyStreet, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, LIC #: 0007002665, Work Type:

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



On Sep 16, 2:13*am, killer six wrote:
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 a
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!

On Sep 8, 9:12*am, Paul Robinson wrote:



Hi
What is the question?
regards
Paul

On Sep 8, 6:21*am, flashback wrote:

I have followed a previous thread on this topichttp://groups.google.com/group/microsoft.public.excel.programming/bro...,
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: , LIC #:
0007002665, Work Type:
2. * * *ABC INC, 0000015583, 1365 MyStreet, U 34, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: , LIC #: 0007002665,
Work Type:
3. * * *ABC INC, 0000015583, 1365 MyStreet, , MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: , LIC #: 0007002665,
Work Type:
4. * * *ABC INC, 0000015583, 1365 MyStreet, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, E: ,
5. * * *ABC INC, 0000015583, MCR1461, 1365 MyStreet, MyCity, L5T 2J5, T:
(123)456-7890, F: 123456-7890, LIC #: 0007002665, Work Type:- Hide quoted text -

- Show quoted text -

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