Use a function to format cells?

[43fan]

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 Ogilvy]

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?

 

[43fan]

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]

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.

 

[43fan]

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 P14 instead of
telling it a sheet name first, same result. What am I doing wrong?

Thanks!
Shawn

 

[43fan]

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]

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]

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

 

[Myrna Larson]

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