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