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