I have a report that I run in Excel, which pulls data from a text file into
a worksheet, placing each value in a cell, and then pulls info from these
cells to create a report. One cell in particular normally has a series of
characters/numbers then a dash, then another series of characters/numbers.
I pull just the characters/numbers to the right of the dash to print on the
report, several of these actually, well, four to be exact, and concatenate
them with a comma separating. Problem is, for one customer in particular,
their item number doesn't have dashes. This then makes the data on the
report look weird with that one cell all stretched out like that, so I
manually set the format of the cell to wrap text, then resize the cell so
all the comma's line up to the right. I'd like to do this either using
worksheet functions(I posted there as well) or, using a "macro" function.

Right now to get the numbers after the dash and put the comma's in where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2) & "," &
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls just the
characters to the right of it. As you can see, I then concatenate them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go ahead and
concatenate them with the commas, but then have the cell automatically wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

If your number doesn't have a dash, what business rule determines which
numbers to include. Is it the rightmost 12 or does the whole cell value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a text file
into
a worksheet, placing each value in a cell, and then pulls info from these
cells to create a report. One cell in particular normally has a series
of
characters/numbers then a dash, then another series of characters/numbers.
I pull just the characters/numbers to the right of the dash to print on
the
report, several of these actually, well, four to be exact, and concatenate
them with a comma separating. Problem is, for one customer in particular,
their item number doesn't have dashes. This then makes the data on the
report look weird with that one cell all stretched out like that, so I
manually set the format of the cell to wrap text, then resize the cell so
all the comma's line up to the right. I'd like to do this either using
worksheet functions(I posted there as well) or, using a "macro" function.

Right now to get the numbers after the dash and put the comma's in where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2) & ","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls just
the
characters to the right of it. As you can see, I then concatenate them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go ahead
and
concatenate them with the commas, but then have the cell automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

Tom,

Here's how the info is in the initial worksheet where I get the data from:
111111
222222
333333
444444

The next set of numbers might be:
11111111
22222222
33333333
44444444

Right now, using the "righttodash" function and the concatenation, these
would be brought into the report like this:
111111,222222,333333,444444
and
11111111,22222222,33333333,44444444
respectively.

I want to format the cell that these are placed in, and have it look like
this:
111111,
222222,
333333,
444444
and
11111111,
22222222,
33333333,
44444444

So I guess what I'm saying is, there isn't any real hard and fast "rule" as
to the length of the number. The only thing with each number is, each one
is listed in a separate cell on the "data" worksheet prior to being pulled
into the report. Also, I know in advance that there will only be four
numbers pulled in.

I suppose a function that would somehow take the first number, add a comma,
then a "carriage return", next number, comma, "carriage return" etc, would
work. But there again, I don't know how to do that within one cell. I
guess the cell would have to be formatted in advance to wrap text as well?
I can have one workbook that is set up to run reports for this one customer
only, so it's not something that has to work along with the regular
concatenation. It can be separate.

I apologize for the long message, but I also hope that I've explained this
better.

Thanks!
Shawn


"Tom Ogilvy" wrote in message
...
If your number doesn't have a dash, what business rule determines which
numbers to include. Is it the rightmost 12 or does the whole cell value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a text file
into
a worksheet, placing each value in a cell, and then pulls info from
these
cells to create a report. One cell in particular normally has a series
of
characters/numbers then a dash, then another series of
characters/numbers.
I pull just the characters/numbers to the right of the dash to print on
the
report, several of these actually, well, four to be exact, and
concatenate
them with a comma separating. Problem is, for one customer in
particular,
their item number doesn't have dashes. This then makes the data on the
report look weird with that one cell all stretched out like that, so I
manually set the format of the cell to wrap text, then resize the cell
so
all the comma's line up to the right. I'd like to do this either using
worksheet functions(I posted there as well) or, using a "macro"
function.

Right now to get the numbers after the dash and put the comma's in where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2) &
","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls just
the
characters to the right of it. As you can see, I then concatenate them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go ahead
and
concatenate them with the commas, but then have the cell automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

Public function Righttodash(rng as Range)
sStr = ""
for each cell in rng
sStr = cell.Value & "," & chr(10)
next
sStr = Left(sStr,len(sStr)-2)
RighttoDash = sStr
End Function

format the cell to wordwrap.

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
Tom,

Here's how the info is in the initial worksheet where I get the data from:
111111
222222
333333
444444

The next set of numbers might be:
11111111
22222222
33333333
44444444

Right now, using the "righttodash" function and the concatenation, these
would be brought into the report like this:
111111,222222,333333,444444
and
11111111,22222222,33333333,44444444
respectively.

I want to format the cell that these are placed in, and have it look like
this:
111111,
222222,
333333,
444444
and
11111111,
22222222,
33333333,
44444444

So I guess what I'm saying is, there isn't any real hard and fast "rule"
as
to the length of the number. The only thing with each number is, each one
is listed in a separate cell on the "data" worksheet prior to being pulled
into the report. Also, I know in advance that there will only be four
numbers pulled in.

I suppose a function that would somehow take the first number, add a
comma,
then a "carriage return", next number, comma, "carriage return" etc, would
work. But there again, I don't know how to do that within one cell. I
guess the cell would have to be formatted in advance to wrap text as well?
I can have one workbook that is set up to run reports for this one
customer
only, so it's not something that has to work along with the regular
concatenation. It can be separate.

I apologize for the long message, but I also hope that I've explained this
better.

Thanks!
Shawn


"Tom Ogilvy" wrote in message
...
If your number doesn't have a dash, what business rule determines which
numbers to include. Is it the rightmost 12 or does the whole cell value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a text file
into
a worksheet, placing each value in a cell, and then pulls info from
these
cells to create a report. One cell in particular normally has a
series
of
characters/numbers then a dash, then another series of
characters/numbers.
I pull just the characters/numbers to the right of the dash to print
on
the
report, several of these actually, well, four to be exact, and
concatenate
them with a comma separating. Problem is, for one customer in
particular,
their item number doesn't have dashes. This then makes the data on
the
report look weird with that one cell all stretched out like that, so I
manually set the format of the cell to wrap text, then resize the cell
so
all the comma's line up to the right. I'd like to do this either
using
worksheet functions(I posted there as well) or, using a "macro"
function.

Right now to get the numbers after the dash and put the comma's in
where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2) &
","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls
just
the
characters to the right of it. As you can see, I then concatenate
them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go
ahead
and
concatenate them with the commas, but then have the cell automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

Tom,

That's very close, but it doesn't quite do it for some reason. The only
thing I've changed is the function name, called it NoDash instead of
RightToDash.

It gives me just the last entry though, instead of all four. I even tried
just using a regular cell reference for a range, like P1:P4 instead of
telling it a sheet name first, same result. What am I doing wrong?

Thanks!
Shawn

"Tom Ogilvy" wrote in message
...
Public function Righttodash(rng as Range)
sStr = ""
for each cell in rng
sStr = cell.Value & "," & chr(10)
next
sStr = Left(sStr,len(sStr)-2)
RighttoDash = sStr
End Function

format the cell to wordwrap.

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
Tom,

Here's how the info is in the initial worksheet where I get the data
from:
111111
222222
333333
444444

The next set of numbers might be:
11111111
22222222
33333333
44444444

Right now, using the "righttodash" function and the concatenation, these
would be brought into the report like this:
111111,222222,333333,444444
and
11111111,22222222,33333333,44444444
respectively.

I want to format the cell that these are placed in, and have it look
like
this:
111111,
222222,
333333,
444444
and
11111111,
22222222,
33333333,
44444444

So I guess what I'm saying is, there isn't any real hard and fast "rule"
as
to the length of the number. The only thing with each number is, each
one
is listed in a separate cell on the "data" worksheet prior to being
pulled
into the report. Also, I know in advance that there will only be four
numbers pulled in.

I suppose a function that would somehow take the first number, add a
comma,
then a "carriage return", next number, comma, "carriage return" etc,
would
work. But there again, I don't know how to do that within one cell. I
guess the cell would have to be formatted in advance to wrap text as
well?
I can have one workbook that is set up to run reports for this one
customer
only, so it's not something that has to work along with the regular
concatenation. It can be separate.

I apologize for the long message, but I also hope that I've explained
this
better.

Thanks!
Shawn


"Tom Ogilvy" wrote in message
...
If your number doesn't have a dash, what business rule determines
which
numbers to include. Is it the rightmost 12 or does the whole cell
value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a text
file
into
a worksheet, placing each value in a cell, and then pulls info from
these
cells to create a report. One cell in particular normally has a
series
of
characters/numbers then a dash, then another series of
characters/numbers.
I pull just the characters/numbers to the right of the dash to print
on
the
report, several of these actually, well, four to be exact, and
concatenate
them with a comma separating. Problem is, for one customer in
particular,
their item number doesn't have dashes. This then makes the data on
the
report look weird with that one cell all stretched out like that, so
I
manually set the format of the cell to wrap text, then resize the
cell
so
all the comma's line up to the right. I'd like to do this either
using
worksheet functions(I posted there as well) or, using a "macro"
function.

Right now to get the numbers after the dash and put the comma's in
where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2)
&
","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls
just
the
characters to the right of it. As you can see, I then concatenate
them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go
ahead
and
concatenate them with the commas, but then have the cell
automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

Tom,

First off, I didn't thank you properly for your help in my last message,
and I want to do that now. :) I'm working on this myself right now too,
trying to figure out what I've done wrong, but so far, no luck. ;) Thanks
so much for your help.

One other thing I've noticed though too is, it doesn't seem to force the
width of the cell to be enough to include the comma. If I set the range to
just one cell, it displays the value from that cell, but if the cell isn't
already wide enough to include the entire value, it just truncates the
display.

If I set the range to be more than one cell, it displays just the last cell
value in the range, but again, if the width isn't already wide enough, it
truncates the display.

Anything you can tell me here is greatly appreciated. :)

Thank you very much!
Shawn

"Tom Ogilvy" wrote in message
...
Public function Righttodash(rng as Range)
sStr = ""
for each cell in rng
sStr = cell.Value & "," & chr(10)
next
sStr = Left(sStr,len(sStr)-2)
RighttoDash = sStr
End Function

format the cell to wordwrap.

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
Tom,

Here's how the info is in the initial worksheet where I get the data
from:
111111
222222
333333
444444

The next set of numbers might be:
11111111
22222222
33333333
44444444

Right now, using the "righttodash" function and the concatenation, these
would be brought into the report like this:
111111,222222,333333,444444
and
11111111,22222222,33333333,44444444
respectively.

I want to format the cell that these are placed in, and have it look
like
this:
111111,
222222,
333333,
444444
and
11111111,
22222222,
33333333,
44444444

So I guess what I'm saying is, there isn't any real hard and fast "rule"
as
to the length of the number. The only thing with each number is, each
one
is listed in a separate cell on the "data" worksheet prior to being
pulled
into the report. Also, I know in advance that there will only be four
numbers pulled in.

I suppose a function that would somehow take the first number, add a
comma,
then a "carriage return", next number, comma, "carriage return" etc,
would
work. But there again, I don't know how to do that within one cell. I
guess the cell would have to be formatted in advance to wrap text as
well?
I can have one workbook that is set up to run reports for this one
customer
only, so it's not something that has to work along with the regular
concatenation. It can be separate.

I apologize for the long message, but I also hope that I've explained
this
better.

Thanks!
Shawn


"Tom Ogilvy" wrote in message
...
If your number doesn't have a dash, what business rule determines
which
numbers to include. Is it the rightmost 12 or does the whole cell
value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a text
file
into
a worksheet, placing each value in a cell, and then pulls info from
these
cells to create a report. One cell in particular normally has a
series
of
characters/numbers then a dash, then another series of
characters/numbers.
I pull just the characters/numbers to the right of the dash to print
on
the
report, several of these actually, well, four to be exact, and
concatenate
them with a comma separating. Problem is, for one customer in
particular,
their item number doesn't have dashes. This then makes the data on
the
report look weird with that one cell all stretched out like that, so
I
manually set the format of the cell to wrap text, then resize the
cell
so
all the comma's line up to the right. I'd like to do this either
using
worksheet functions(I posted there as well) or, using a "macro"
function.

Right now to get the numbers after the dash and put the comma's in
where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2)
&
","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls
just
the
characters to the right of it. As you can see, I then concatenate
them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go
ahead
and
concatenate them with the commas, but then have the cell
automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

My fault, left the "accumulator" part out:

Public Function Righttodash(rng As Range)
Dim sStr As String
sStr = ""
For Each cell In rng
sStr = sStr & cell.Value & "," & Chr(10)
Next
sStr = Left(sStr, Len(sStr) - 2)
Righttodash = sStr
End Function

The function can't make any adjustments to the cell. It just isn't allowed.
If you haven't set the width or hight, then cells should autofit
automatically. You do have to format the cells to wraptext.

==
regards,
tom Ogilvy

"43fan" wrote in message
...
Tom,

First off, I didn't thank you properly for your help in my last message,
and I want to do that now. :) I'm working on this myself right now too,
trying to figure out what I've done wrong, but so far, no luck. ;) Thanks
so much for your help.

One other thing I've noticed though too is, it doesn't seem to force the
width of the cell to be enough to include the comma. If I set the range
to
just one cell, it displays the value from that cell, but if the cell isn't
already wide enough to include the entire value, it just truncates the
display.

If I set the range to be more than one cell, it displays just the last
cell
value in the range, but again, if the width isn't already wide enough, it
truncates the display.

Anything you can tell me here is greatly appreciated. :)

Thank you very much!
Shawn

"Tom Ogilvy" wrote in message
...
Public function Righttodash(rng as Range)
sStr = ""
for each cell in rng
sStr = cell.Value & "," & chr(10)
next
sStr = Left(sStr,len(sStr)-2)
RighttoDash = sStr
End Function

format the cell to wordwrap.

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
Tom,

Here's how the info is in the initial worksheet where I get the data
from:
111111
222222
333333
444444

The next set of numbers might be:
11111111
22222222
33333333
44444444

Right now, using the "righttodash" function and the concatenation,
these
would be brought into the report like this:
111111,222222,333333,444444
and
11111111,22222222,33333333,44444444
respectively.

I want to format the cell that these are placed in, and have it look
like
this:
111111,
222222,
333333,
444444
and
11111111,
22222222,
33333333,
44444444

So I guess what I'm saying is, there isn't any real hard and fast
"rule"
as
to the length of the number. The only thing with each number is, each
one
is listed in a separate cell on the "data" worksheet prior to being
pulled
into the report. Also, I know in advance that there will only be four
numbers pulled in.

I suppose a function that would somehow take the first number, add a
comma,
then a "carriage return", next number, comma, "carriage return" etc,
would
work. But there again, I don't know how to do that within one cell.
I
guess the cell would have to be formatted in advance to wrap text as
well?
I can have one workbook that is set up to run reports for this one
customer
only, so it's not something that has to work along with the regular
concatenation. It can be separate.

I apologize for the long message, but I also hope that I've explained
this
better.

Thanks!
Shawn


"Tom Ogilvy" wrote in message
...
If your number doesn't have a dash, what business rule determines
which
numbers to include. Is it the rightmost 12 or does the whole cell
value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a text
file
into
a worksheet, placing each value in a cell, and then pulls info
from
these
cells to create a report. One cell in particular normally has a
series
of
characters/numbers then a dash, then another series of
characters/numbers.
I pull just the characters/numbers to the right of the dash to
print
on
the
report, several of these actually, well, four to be exact, and
concatenate
them with a comma separating. Problem is, for one customer in
particular,
their item number doesn't have dashes. This then makes the data
on
the
report look weird with that one cell all stretched out like that,
so
I
manually set the format of the cell to wrap text, then resize the
cell
so
all the comma's line up to the right. I'd like to do this either
using
worksheet functions(I posted there as well) or, using a "macro"
function.

Right now to get the numbers after the dash and put the comma's in
where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1
Data'!$J2)
&
","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1
Data'!$J4)

with "rightofdash" being a function that finds the dash, then
pulls
just
the
characters to the right of it. As you can see, I then concatenate
them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go
ahead
and
concatenate them with the commas, but then have the cell
automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

Tom,

Thanks! That did the trick completely! I probably should have noticed that,
but looked past it the whole time.

Thanks so very much!
Shawn

"Tom Ogilvy" wrote in message
...
My fault, left the "accumulator" part out:

Public Function Righttodash(rng As Range)
Dim sStr As String
sStr = ""
For Each cell In rng
sStr = sStr & cell.Value & "," & Chr(10)
Next
sStr = Left(sStr, Len(sStr) - 2)
Righttodash = sStr
End Function

The function can't make any adjustments to the cell. It just isn't
allowed.
If you haven't set the width or hight, then cells should autofit
automatically. You do have to format the cells to wraptext.

==
regards,
tom Ogilvy

"43fan" wrote in message
...
Tom,

First off, I didn't thank you properly for your help in my last
message,
and I want to do that now. :) I'm working on this myself right now too,
trying to figure out what I've done wrong, but so far, no luck. ;)
Thanks
so much for your help.

One other thing I've noticed though too is, it doesn't seem to force the
width of the cell to be enough to include the comma. If I set the range
to
just one cell, it displays the value from that cell, but if the cell
isn't
already wide enough to include the entire value, it just truncates the
display.

If I set the range to be more than one cell, it displays just the last
cell
value in the range, but again, if the width isn't already wide enough,
it
truncates the display.

Anything you can tell me here is greatly appreciated. :)

Thank you very much!
Shawn

"Tom Ogilvy" wrote in message
...
Public function Righttodash(rng as Range)
sStr = ""
for each cell in rng
sStr = cell.Value & "," & chr(10)
next
sStr = Left(sStr,len(sStr)-2)
RighttoDash = sStr
End Function

format the cell to wordwrap.

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
Tom,

Here's how the info is in the initial worksheet where I get the data
from:
111111
222222
333333
444444

The next set of numbers might be:
11111111
22222222
33333333
44444444

Right now, using the "righttodash" function and the concatenation,
these
would be brought into the report like this:
111111,222222,333333,444444
and
11111111,22222222,33333333,44444444
respectively.

I want to format the cell that these are placed in, and have it look
like
this:
111111,
222222,
333333,
444444
and
11111111,
22222222,
33333333,
44444444

So I guess what I'm saying is, there isn't any real hard and fast
"rule"
as
to the length of the number. The only thing with each number is,
each
one
is listed in a separate cell on the "data" worksheet prior to being
pulled
into the report. Also, I know in advance that there will only be
four
numbers pulled in.

I suppose a function that would somehow take the first number, add a
comma,
then a "carriage return", next number, comma, "carriage return" etc,
would
work. But there again, I don't know how to do that within one cell.
I
guess the cell would have to be formatted in advance to wrap text as
well?
I can have one workbook that is set up to run reports for this one
customer
only, so it's not something that has to work along with the regular
concatenation. It can be separate.

I apologize for the long message, but I also hope that I've
explained
this
better.

Thanks!
Shawn


"Tom Ogilvy" wrote in message
...
If your number doesn't have a dash, what business rule determines
which
numbers to include. Is it the rightmost 12 or does the whole cell
value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a
text
file
into
a worksheet, placing each value in a cell, and then pulls info
from
these
cells to create a report. One cell in particular normally has
a
series
of
characters/numbers then a dash, then another series of
characters/numbers.
I pull just the characters/numbers to the right of the dash to
print
on
the
report, several of these actually, well, four to be exact, and
concatenate
them with a comma separating. Problem is, for one customer in
particular,
their item number doesn't have dashes. This then makes the data
on
the
report look weird with that one cell all stretched out like
that,
so
I
manually set the format of the cell to wrap text, then resize
the
cell
so
all the comma's line up to the right. I'd like to do this
either
using
worksheet functions(I posted there as well) or, using a "macro"
function.

Right now to get the numbers after the dash and put the comma's
in
where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1
Data'!$J2)
&
","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1
Data'!$J4)

with "rightofdash" being a function that finds the dash, then
pulls
just
the
characters to the right of it. As you can see, I then
concatenate
them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is
go
ahead
and
concatenate them with the commas, but then have the cell
automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn

I think Tom had a typo. He was forgetting to add the new text to what had been
accumulated so far. Try it this way

Public function Righttodash(Rng as Range) As String
Dim Cell As Range
Dim sSep As String
Dim sStr As String

sSep = "," & Chr$(10)
sStr = ""
For Each Cell in Rng
sStr = sStr & sSep & Cell.Value
Next Cell
RighttoDash = Mid$(sStr, 3)
End Function

On Wed, 2 Mar 2005 13:42:44 -0500, "43fan" wrote:

Tom,

That's very close, but it doesn't quite do it for some reason. The only
thing I've changed is the function name, called it NoDash instead of
RightToDash.

It gives me just the last entry though, instead of all four. I even tried
just using a regular cell reference for a range, like P1:P4 instead of
telling it a sheet name first, same result. What am I doing wrong?

Thanks!
Shawn

"Tom Ogilvy" wrote in message
...
Public function Righttodash(rng as Range)
sStr = ""
for each cell in rng
sStr = cell.Value & "," & chr(10)
next
sStr = Left(sStr,len(sStr)-2)
RighttoDash = sStr
End Function

format the cell to wordwrap.

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
Tom,

Here's how the info is in the initial worksheet where I get the data
from:
111111
222222
333333
444444

The next set of numbers might be:
11111111
22222222
33333333
44444444

Right now, using the "righttodash" function and the concatenation, these
would be brought into the report like this:
111111,222222,333333,444444
and
11111111,22222222,33333333,44444444
respectively.

I want to format the cell that these are placed in, and have it look
like
this:
111111,
222222,
333333,
444444
and
11111111,
22222222,
33333333,
44444444

So I guess what I'm saying is, there isn't any real hard and fast "rule"
as
to the length of the number. The only thing with each number is, each
one
is listed in a separate cell on the "data" worksheet prior to being
pulled
into the report. Also, I know in advance that there will only be four
numbers pulled in.

I suppose a function that would somehow take the first number, add a
comma,
then a "carriage return", next number, comma, "carriage return" etc,
would
work. But there again, I don't know how to do that within one cell. I
guess the cell would have to be formatted in advance to wrap text as
well?
I can have one workbook that is set up to run reports for this one
customer
only, so it's not something that has to work along with the regular
concatenation. It can be separate.

I apologize for the long message, but I also hope that I've explained
this
better.

Thanks!
Shawn


"Tom Ogilvy" wrote in message
...
If your number doesn't have a dash, what business rule determines
which
numbers to include. Is it the rightmost 12 or does the whole cell
value
need to be processed. What are the rules for the commas - every 4
characters?

--
Regards,
Tom Ogilvy

"43fan" wrote in message
...
I have a report that I run in Excel, which pulls data from a text
file
into
a worksheet, placing each value in a cell, and then pulls info from
these
cells to create a report. One cell in particular normally has a
series
of
characters/numbers then a dash, then another series of
characters/numbers.
I pull just the characters/numbers to the right of the dash to print
on
the
report, several of these actually, well, four to be exact, and
concatenate
them with a comma separating. Problem is, for one customer in
particular,
their item number doesn't have dashes. This then makes the data on
the
report look weird with that one cell all stretched out like that, so
I
manually set the format of the cell to wrap text, then resize the
cell
so
all the comma's line up to the right. I'd like to do this either
using
worksheet functions(I posted there as well) or, using a "macro"
function.

Right now to get the numbers after the dash and put the comma's in
where
they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2)
&
","
&
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls
just
the
characters to the right of it. As you can see, I then concatenate
them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go
ahead
and
concatenate them with the commas, but then have the cell
automatically
wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Thanks!!
Shawn