I am not sure

Hello;

I am trying to help someone, for which I am not sure there exists a
solution. He has a .csv file that he is opening in Excel. The values,
somehow, import into the correct conglomeration of data. More in a minute.

Column A is a number formatted as text. Column B is a series of numbers,
some formatted as text. Those that are not formatted as text, are separated
by commas (Huh!!) and hyphens.

The object is to concatenate the two values together. Example:

A | B | C
| |
1 | 357 |Argentina
| |
345 |32,34,39 |Paraguay
| 40-45 |
| |
33945 |27-30,41, | USA
|43, 45 |

The data should finally look like this for USA (etc.)

A | B
3394527 |USA
3394528 |USA
3394529 |USA
3394530 |USA
3394541 |USA
3394543 |USA
3394545 |USA

Which column the new values end up in does not matter. I know how to do this
fairly rapidly with formula's and a big enough worksheet. Then copy and paste
the values to where I want them.

I am wondering if this is even possible. I am not a big VB fan. I like to
avoid them if I can, but I wonder if this can even be handeled by VB. Column
B on one of the cells looks like this as an example.

264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584,
264724, 264729, 264772-264773

There is no regularity to the number of digits. There is no regularity to
the hyphenation location. There is no regularity to the format of the numbers
(which I guess all could be changed to numbers and back to text for
concantenation) and there is no regularity to the quantity of values in the
cell.

Is there any hope for this? Any help would be appreciated.

Thank You

Frank Pytel

PS. I see a lot of posts here that look really nice. I am on the Office
Online site. I know these posts can be viewed and edited at other sites. Can
I set this up in Outlook so that I can Post and search. Kind of like Outlook
Express? Thanks

BobBridges
Thu May 08 19:53:01 CDT 2008

It absolutely can be done in VB; in fact I would say, theoretically, that any
full programming language can do it, even if some can do it more easily than
others. This one would be easier to do in other languages, but it wouldn't
be really easy in any language - possibly excepting REXX, and maybe LISP if I
knew LISP - and if Excel is where the data is stored then it's probably the
right place to process it.

Let's see, now. There may be a more elegant way to do it, but I think each
string in column B can be parsed as follows:

1) Change every string of consecutive <space>s to a single <space>.
2) Next change every occurrence of <space><hyphen> and <hyphen><space> to a
single <hyphen>.
3) Next change every occurrence of <comma>, <space><comma> and
<comma><space> to a single <space>.

Now you have a string consisting simply of "<i> <j>-<k> <l>" etc. You can
take each space-delimited group in turn. Each one that has a hyphen in it
can be divided and then turned into a list of numbers <j> through <k>, and
each group without a hyphen is number by itself. Each number thus generated
must be concatenated to the value in column A and written to a new cell in
your resulting list.

As I said, this isn't exactly elegant, but it's more easily described than
some of the solutions that would run faster. How much detail do you want to
get into?

--- "Frank Pytel" wrote:
> I am trying to help someone, for which I am not sure there exists a solution. He
> has a .csv file that he is opening in Excel. The values, somehow, import into the
> correct conglomeration of data. More in a minute.
>
> Column A is a number formatted as text. Column B is a series of numbers, some
> formatted as text. Those that are not formatted as text, are separated by
> commas (Huh!!) and hyphens.
>
> The object is to concatenate the two values together. Example:
>
> A | B | C
> | |
> 1 | 357 |Argentina
> | |
> 345 |32,34,39 |Paraguay
> | 40-45 |
> | |
> 33945 |27-30,41, | USA
> |43, 45 |
>
> The data should finally look like this for USA (etc.)
>
> A | B
> 3394527 |USA
> 3394528 |USA
> 3394529 |USA
> 3394530 |USA
> 3394541 |USA
> 3394543 |USA
> 3394545 |USA
>
> Which column the new values end up in does not matter. I know how to do this
> fairly rapidly with formula's and a big enough worksheet. Then copy and paste
> the values to where I want them.
>
> I am wondering if this is even possible. I am not a big VB fan. I like to
> avoid them if I can, but I wonder if this can even be handeled by VB. Column
> B on one of the cells looks like this as an example.
>
> 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584,
> 264724, 264729, 264772-264773
>
> There is no regularity to the number of digits. There is no regularity to
> the hyphenation location. There is no regularity to the format of the numbers
> (which I guess all could be changed to numbers and back to text for
> concantenation) and there is no regularity to the quantity of values in the
> cell.
>
> Is there any hope for this? Any help would be appreciated.
>
> PS. I see a lot of posts here that look really nice. I am on the Office
> Online site. I know these posts can be viewed and edited at other sites. Can
> I set this up in Outlook so that I can Post and search. Kind of like Outlook
> Express? Thanks

Jeff
Fri May 09 10:12:53 CDT 2008

"Frank Pytel" <fpytel@sc.rr.com.do.not.spam.jerk> wrote in message
news:B898F124-AE3D-4143-A14E-47C062548424@microsoft.com...

> PS. I see a lot of posts here that look really nice. I am on the Office
> Online site. I know these posts can be viewed and edited at other sites.
> Can
> I set this up in Outlook so that I can Post and search. Kind of like
> Outlook
> Express? Thanks

Outlook Express is where you want to be. It's a newsreader, and this is a
newsgroup, NOT a Web site, even though you may be reading it through a Web
application which makes it APPEAR to be a Web site/forum.

Anyways, in OE, set up a News account and point it to msnews.microsoft.com,
then subscribe to microsoft.public.excel.programming.

Of course, there are other newsreaders out there, some even free, but OE is
basically guaranteed to already be on the (Windows) machine you're using.

fpytel
Fri May 09 11:51:03 CDT 2008

Thanks Jeff;

Frank Pytel

"Jeff Johnson" wrote:

> "Frank Pytel" <fpytel@sc.rr.com.do.not.spam.jerk> wrote in message
> news:B898F124-AE3D-4143-A14E-47C062548424@microsoft.com...
>
> > PS. I see a lot of posts here that look really nice. I am on the Office
> > Online site. I know these posts can be viewed and edited at other sites.
> > Can
> > I set this up in Outlook so that I can Post and search. Kind of like
> > Outlook
> > Express? Thanks
>
> Outlook Express is where you want to be. It's a newsreader, and this is a
> newsgroup, NOT a Web site, even though you may be reading it through a Web
> application which makes it APPEAR to be a Web site/forum.
>
> Anyways, in OE, set up a News account and point it to msnews.microsoft.com,
> then subscribe to microsoft.public.excel.programming.
>
> Of course, there are other newsreaders out there, some even free, but OE is
> basically guaranteed to already be on the (Windows) machine you're using.
>
>
>

fpytel
Fri May 09 11:54:03 CDT 2008

Bob;

Sounds easy enough. I would certainly like to see code to this affect. The
VB that I know is limited, but it sounds intriguing. I am not sure at all
that I see this working.

Can you point me to some of the code for the hyphen portion and for deleting
commas.

Thanks Bob. Have a Great Day.

Frank Pytel

"Bob Bridges" wrote:

> It absolutely can be done in VB; in fact I would say, theoretically, that any
> full programming language can do it, even if some can do it more easily than
> others. This one would be easier to do in other languages, but it wouldn't
> be really easy in any language - possibly excepting REXX, and maybe LISP if I
> knew LISP - and if Excel is where the data is stored then it's probably the
> right place to process it.
>
> Let's see, now. There may be a more elegant way to do it, but I think each
> string in column B can be parsed as follows:
>
> 1) Change every string of consecutive <space>s to a single <space>.
> 2) Next change every occurrence of <space><hyphen> and <hyphen><space> to a
> single <hyphen>.
> 3) Next change every occurrence of <comma>, <space><comma> and
> <comma><space> to a single <space>.
>
> Now you have a string consisting simply of "<i> <j>-<k> <l>" etc. You can
> take each space-delimited group in turn. Each one that has a hyphen in it
> can be divided and then turned into a list of numbers <j> through <k>, and
> each group without a hyphen is number by itself. Each number thus generated
> must be concatenated to the value in column A and written to a new cell in
> your resulting list.
>
> As I said, this isn't exactly elegant, but it's more easily described than
> some of the solutions that would run faster. How much detail do you want to
> get into?
>
> --- "Frank Pytel" wrote:
> > I am trying to help someone, for which I am not sure there exists a solution. He
> > has a .csv file that he is opening in Excel. The values, somehow, import into the
> > correct conglomeration of data. More in a minute.
> >
> > Column A is a number formatted as text. Column B is a series of numbers, some
> > formatted as text. Those that are not formatted as text, are separated by
> > commas (Huh!!) and hyphens.
> >
> > The object is to concatenate the two values together. Example:
> >
> > A | B | C
> > | |
> > 1 | 357 |Argentina
> > | |
> > 345 |32,34,39 |Paraguay
> > | 40-45 |
> > | |
> > 33945 |27-30,41, | USA
> > |43, 45 |
> >
> > The data should finally look like this for USA (etc.)
> >
> > A | B
> > 3394527 |USA
> > 3394528 |USA
> > 3394529 |USA
> > 3394530 |USA
> > 3394541 |USA
> > 3394543 |USA
> > 3394545 |USA
> >
> > Which column the new values end up in does not matter. I know how to do this
> > fairly rapidly with formula's and a big enough worksheet. Then copy and paste
> > the values to where I want them.
> >
> > I am wondering if this is even possible. I am not a big VB fan. I like to
> > avoid them if I can, but I wonder if this can even be handeled by VB. Column
> > B on one of the cells looks like this as an example.
> >
> > 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584,
> > 264724, 264729, 264772-264773
> >
> > There is no regularity to the number of digits. There is no regularity to
> > the hyphenation location. There is no regularity to the format of the numbers
> > (which I guess all could be changed to numbers and back to text for
> > concantenation) and there is no regularity to the quantity of values in the
> > cell.
> >
> > Is there any hope for this? Any help would be appreciated.
> >
> > PS. I see a lot of posts here that look really nice. I am on the Office
> > Online site. I know these posts can be viewed and edited at other sites. Can
> > I set this up in Outlook so that I can Post and search. Kind of like Outlook
> > Express? Thanks

Rick
Fri May 09 15:12:34 CDT 2008

The VB I know is not all that limited at all. Just out of curiosity, which
version of VBA are you using?

Rick


"Frank Pytel" <fpytel@sc.rr.com.do.not.spam.jerk> wrote in message
news:0DEDB971-1D7F-446F-83EB-A2C9473BCEE3@microsoft.com...
> Bob;
>
> Sounds easy enough. I would certainly like to see code to this affect. The
> VB that I know is limited, but it sounds intriguing. I am not sure at all
> that I see this working.
>
> Can you point me to some of the code for the hyphen portion and for
> deleting
> commas.
>
> Thanks Bob. Have a Great Day.
>
> Frank Pytel
>
> "Bob Bridges" wrote:
>
>> It absolutely can be done in VB; in fact I would say, theoretically, that
>> any
>> full programming language can do it, even if some can do it more easily
>> than
>> others. This one would be easier to do in other languages, but it
>> wouldn't
>> be really easy in any language - possibly excepting REXX, and maybe LISP
>> if I
>> knew LISP - and if Excel is where the data is stored then it's probably
>> the
>> right place to process it.
>>
>> Let's see, now. There may be a more elegant way to do it, but I think
>> each
>> string in column B can be parsed as follows:
>>
>> 1) Change every string of consecutive <space>s to a single <space>.
>> 2) Next change every occurrence of <space><hyphen> and <hyphen><space> to
>> a
>> single <hyphen>.
>> 3) Next change every occurrence of <comma>, <space><comma> and
>> <comma><space> to a single <space>.
>>
>> Now you have a string consisting simply of "<i> <j>-<k> <l>" etc. You
>> can
>> take each space-delimited group in turn. Each one that has a hyphen in
>> it
>> can be divided and then turned into a list of numbers <j> through <k>,
>> and
>> each group without a hyphen is number by itself. Each number thus
>> generated
>> must be concatenated to the value in column A and written to a new cell
>> in
>> your resulting list.
>>
>> As I said, this isn't exactly elegant, but it's more easily described
>> than
>> some of the solutions that would run faster. How much detail do you want
>> to
>> get into?
>>
>> --- "Frank Pytel" wrote:
>> > I am trying to help someone, for which I am not sure there exists a
>> > solution. He
>> > has a .csv file that he is opening in Excel. The values, somehow,
>> > import into the
>> > correct conglomeration of data. More in a minute.
>> >
>> > Column A is a number formatted as text. Column B is a series of
>> > numbers, some
>> > formatted as text. Those that are not formatted as text, are separated
>> > by
>> > commas (Huh!!) and hyphens.
>> >
>> > The object is to concatenate the two values together. Example:
>> >
>> > A | B | C
>> > | |
>> > 1 | 357 |Argentina
>> > | |
>> > 345 |32,34,39 |Paraguay
>> > | 40-45 |
>> > | |
>> > 33945 |27-30,41, | USA
>> > |43, 45 |
>> >
>> > The data should finally look like this for USA (etc.)
>> >
>> > A | B
>> > 3394527 |USA
>> > 3394528 |USA
>> > 3394529 |USA
>> > 3394530 |USA
>> > 3394541 |USA
>> > 3394543 |USA
>> > 3394545 |USA
>> >
>> > Which column the new values end up in does not matter. I know how to do
>> > this
>> > fairly rapidly with formula's and a big enough worksheet. Then copy and
>> > paste
>> > the values to where I want them.
>> >
>> > I am wondering if this is even possible. I am not a big VB fan. I like
>> > to
>> > avoid them if I can, but I wonder if this can even be handeled by VB.
>> > Column
>> > B on one of the cells looks like this as an example.
>> >
>> > 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584,
>> > 264724, 264729, 264772-264773
>> >
>> > There is no regularity to the number of digits. There is no regularity
>> > to
>> > the hyphenation location. There is no regularity to the format of the
>> > numbers
>> > (which I guess all could be changed to numbers and back to text for
>> > concantenation) and there is no regularity to the quantity of values in
>> > the
>> > cell.
>> >
>> > Is there any hope for this? Any help would be appreciated.
>> >
>> > PS. I see a lot of posts here that look really nice. I am on the Office
>> > Online site. I know these posts can be viewed and edited at other
>> > sites. Can
>> > I set this up in Outlook so that I can Post and search. Kind of like
>> > Outlook
>> > Express? Thanks

fpytel
Fri May 09 16:33:02 CDT 2008

Hey Rick;

I am not sure. I don't do much. It says 6.5.1024 is the retail version. It
is called Mircorsoft Visual Basic 6.5.

God Bless

Frank Pytel

"Rick Rothstein (MVP - VB)" wrote:

> The VB I know is not all that limited at all. Just out of curiosity, which
> version of VBA are you using?
>
> Rick
>
>
> "Frank Pytel" <fpytel@sc.rr.com.do.not.spam.jerk> wrote in message
> news:0DEDB971-1D7F-446F-83EB-A2C9473BCEE3@microsoft.com...
> > Bob;
> >
> > Sounds easy enough. I would certainly like to see code to this affect. The
> > VB that I know is limited, but it sounds intriguing. I am not sure at all
> > that I see this working.
> >
> > Can you point me to some of the code for the hyphen portion and for
> > deleting
> > commas.
> >
> > Thanks Bob. Have a Great Day.
> >
> > Frank Pytel
> >
> > "Bob Bridges" wrote:
> >
> >> It absolutely can be done in VB; in fact I would say, theoretically, that
> >> any
> >> full programming language can do it, even if some can do it more easily
> >> than
> >> others. This one would be easier to do in other languages, but it
> >> wouldn't
> >> be really easy in any language - possibly excepting REXX, and maybe LISP
> >> if I
> >> knew LISP - and if Excel is where the data is stored then it's probably
> >> the
> >> right place to process it.
> >>
> >> Let's see, now. There may be a more elegant way to do it, but I think
> >> each
> >> string in column B can be parsed as follows:
> >>
> >> 1) Change every string of consecutive <space>s to a single <space>.
> >> 2) Next change every occurrence of <space><hyphen> and <hyphen><space> to
> >> a
> >> single <hyphen>.
> >> 3) Next change every occurrence of <comma>, <space><comma> and
> >> <comma><space> to a single <space>.
> >>
> >> Now you have a string consisting simply of "<i> <j>-<k> <l>" etc. You
> >> can
> >> take each space-delimited group in turn. Each one that has a hyphen in
> >> it
> >> can be divided and then turned into a list of numbers <j> through <k>,
> >> and
> >> each group without a hyphen is number by itself. Each number thus
> >> generated
> >> must be concatenated to the value in column A and written to a new cell
> >> in
> >> your resulting list.
> >>
> >> As I said, this isn't exactly elegant, but it's more easily described
> >> than
> >> some of the solutions that would run faster. How much detail do you want
> >> to
> >> get into?
> >>
> >> --- "Frank Pytel" wrote:
> >> > I am trying to help someone, for which I am not sure there exists a
> >> > solution. He
> >> > has a .csv file that he is opening in Excel. The values, somehow,
> >> > import into the
> >> > correct conglomeration of data. More in a minute.
> >> >
> >> > Column A is a number formatted as text. Column B is a series of
> >> > numbers, some
> >> > formatted as text. Those that are not formatted as text, are separated
> >> > by
> >> > commas (Huh!!) and hyphens.
> >> >
> >> > The object is to concatenate the two values together. Example:
> >> >
> >> > A | B | C
> >> > | |
> >> > 1 | 357 |Argentina
> >> > | |
> >> > 345 |32,34,39 |Paraguay
> >> > | 40-45 |
> >> > | |
> >> > 33945 |27-30,41, | USA
> >> > |43, 45 |
> >> >
> >> > The data should finally look like this for USA (etc.)
> >> >
> >> > A | B
> >> > 3394527 |USA
> >> > 3394528 |USA
> >> > 3394529 |USA
> >> > 3394530 |USA
> >> > 3394541 |USA
> >> > 3394543 |USA
> >> > 3394545 |USA
> >> >
> >> > Which column the new values end up in does not matter. I know how to do
> >> > this
> >> > fairly rapidly with formula's and a big enough worksheet. Then copy and
> >> > paste
> >> > the values to where I want them.
> >> >
> >> > I am wondering if this is even possible. I am not a big VB fan. I like
> >> > to
> >> > avoid them if I can, but I wonder if this can even be handeled by VB.
> >> > Column
> >> > B on one of the cells looks like this as an example.
> >> >
> >> > 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584,
> >> > 264724, 264729, 264772-264773
> >> >
> >> > There is no regularity to the number of digits. There is no regularity
> >> > to
> >> > the hyphenation location. There is no regularity to the format of the
> >> > numbers
> >> > (which I guess all could be changed to numbers and back to text for
> >> > concantenation) and there is no regularity to the quantity of values in
> >> > the
> >> > cell.
> >> >
> >> > Is there any hope for this? Any help would be appreciated.
> >> >
> >> > PS. I see a lot of posts here that look really nice. I am on the Office
> >> > Online site. I know these posts can be viewed and edited at other
> >> > sites. Can
> >> > I set this up in Outlook so that I can Post and search. Kind of like
> >> > Outlook
> >> > Express? Thanks
>
>

fpytel
Fri May 09 16:34:04 CDT 2008

Hey Rick;

My VBA is limited, not the programs I am sure.

Frank Pytel

"Rick Rothstein (MVP - VB)" wrote:

> The VB I know is not all that limited at all. Just out of curiosity, which
> version of VBA are you using?
>
> Rick
>
>
> "Frank Pytel" <fpytel@sc.rr.com.do.not.spam.jerk> wrote in message
> news:0DEDB971-1D7F-446F-83EB-A2C9473BCEE3@microsoft.com...
> > Bob;
> >
> > Sounds easy enough. I would certainly like to see code to this affect. The
> > VB that I know is limited, but it sounds intriguing. I am not sure at all
> > that I see this working.
> >
> > Can you point me to some of the code for the hyphen portion and for
> > deleting
> > commas.
> >
> > Thanks Bob. Have a Great Day.
> >
> > Frank Pytel
> >
> > "Bob Bridges" wrote:
> >
> >> It absolutely can be done in VB; in fact I would say, theoretically, that
> >> any
> >> full programming language can do it, even if some can do it more easily
> >> than
> >> others. This one would be easier to do in other languages, but it
> >> wouldn't
> >> be really easy in any language - possibly excepting REXX, and maybe LISP
> >> if I
> >> knew LISP - and if Excel is where the data is stored then it's probably
> >> the
> >> right place to process it.
> >>
> >> Let's see, now. There may be a more elegant way to do it, but I think
> >> each
> >> string in column B can be parsed as follows:
> >>
> >> 1) Change every string of consecutive <space>s to a single <space>.
> >> 2) Next change every occurrence of <space><hyphen> and <hyphen><space> to
> >> a
> >> single <hyphen>.
> >> 3) Next change every occurrence of <comma>, <space><comma> and
> >> <comma><space> to a single <space>.
> >>
> >> Now you have a string consisting simply of "<i> <j>-<k> <l>" etc. You
> >> can
> >> take each space-delimited group in turn. Each one that has a hyphen in
> >> it
> >> can be divided and then turned into a list of numbers <j> through <k>,
> >> and
> >> each group without a hyphen is number by itself. Each number thus
> >> generated
> >> must be concatenated to the value in column A and written to a new cell
> >> in
> >> your resulting list.
> >>
> >> As I said, this isn't exactly elegant, but it's more easily described
> >> than
> >> some of the solutions that would run faster. How much detail do you want
> >> to
> >> get into?
> >>
> >> --- "Frank Pytel" wrote:
> >> > I am trying to help someone, for which I am not sure there exists a
> >> > solution. He
> >> > has a .csv file that he is opening in Excel. The values, somehow,
> >> > import into the
> >> > correct conglomeration of data. More in a minute.
> >> >
> >> > Column A is a number formatted as text. Column B is a series of
> >> > numbers, some
> >> > formatted as text. Those that are not formatted as text, are separated
> >> > by
> >> > commas (Huh!!) and hyphens.
> >> >
> >> > The object is to concatenate the two values together. Example:
> >> >
> >> > A | B | C
> >> > | |
> >> > 1 | 357 |Argentina
> >> > | |
> >> > 345 |32,34,39 |Paraguay
> >> > | 40-45 |
> >> > | |
> >> > 33945 |27-30,41, | USA
> >> > |43, 45 |
> >> >
> >> > The data should finally look like this for USA (etc.)
> >> >
> >> > A | B
> >> > 3394527 |USA
> >> > 3394528 |USA
> >> > 3394529 |USA
> >> > 3394530 |USA
> >> > 3394541 |USA
> >> > 3394543 |USA
> >> > 3394545 |USA
> >> >
> >> > Which column the new values end up in does not matter. I know how to do
> >> > this
> >> > fairly rapidly with formula's and a big enough worksheet. Then copy and
> >> > paste
> >> > the values to where I want them.
> >> >
> >> > I am wondering if this is even possible. I am not a big VB fan. I like
> >> > to
> >> > avoid them if I can, but I wonder if this can even be handeled by VB.
> >> > Column
> >> > B on one of the cells looks like this as an example.
> >> >
> >> > 264235, 264469, 264476, 264497, 264536-264539, 264543, 264581-264584,
> >> > 264724, 264729, 264772-264773
> >> >
> >> > There is no regularity to the number of digits. There is no regularity
> >> > to
> >> > the hyphenation location. There is no regularity to the format of the
> >> > numbers
> >> > (which I guess all could be changed to numbers and back to text for
> >> > concantenation) and there is no regularity to the quantity of values in
> >> > the
> >> > cell.
> >> >
> >> > Is there any hope for this? Any help would be appreciated.
> >> >
> >> > PS. I see a lot of posts here that look really nice. I am on the Office
> >> > Online site. I know these posts can be viewed and edited at other
> >> > sites. Can
> >> > I set this up in Outlook so that I can Post and search. Kind of like
> >> > Outlook
> >> > Express? Thanks
>
>