Public Function DashOrNot(rng As Range)
Dim sStr1 as String
Dim sStr As String
Dim cell as Range
Dim bDash as Boolean
sStr = ""
For Each cell In rng
sStr1 = cell.Value
bDash = False
if instr(sStr1,"-") then
sStr1 = Mid(sStr1,instr(sStr1)+1)
bDash = True
end if
sStr = sStr & Trim(sStr1) & "," & _
iif(bDash,vbNullChar,chr(10))
Next
sStr = Left(sStr, Len(sStr) - 2)
DashOrNot = sStr
End Function

Should handle either situation, but won't solve your wrap problem - unless
you make the column wide enough so the string won't wrap unless it contains
the chr(10) in it. Then you could format the column for wraptext and it
wouldn't affect the dash rows.

--
Regards,
Tom Ogilvy






"43fan" wrote in message
...
I got a very good answer to a question in here just the other day, and it
works great! I'm trying to add to it now though, and can't figure out how
to get it to work.

Here's the code Mr. Ogilvy so graciously supplied, which as I said works
great:

Public Function NoDash(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)
NoDash = sStr

End Function

However, for the cell to be "formatted" correctly, it needs to have the
wrap
text attribute set to true.

I use a second function to strip just the portion after the dash(if it
exists) as well, like this:

Function RightOfDash(S As String) As String

Dim Pos As Integer
Pos = InStr(1, S, "-", vbTextCompare)
If Pos 0 Then
RightOfDash = Mid(S, Pos + 1)
Else
RightOfDash = S
End If
End Function

What I'd like to do is us an if statement in the cell to decide which of
these to run, something like this:
=(IF(ISNUMBER(FIND("-",'Veh 1 Data'!J1)),rightofdash('Veh 1
Data'!J1)&","&rightofdash('Veh 1 Data'!J2)&","&rightofdash('Veh 1
Data'!J3)&","&rightofdash('Veh 1 Data'!J4),NoDash('Veh 1 Data'!J1:J4)))

But, I can't figure out how to turn the wrap text attribute on if the dash
doesn't exist, and turn it off if it does. I've tried using :
Worksheets("Tire Wear Summary").Range("C15").WrapText = (then set to true
or
false, depending on which function it's in), but this has no effect.

Is there any way to accomplish this? If not, it's no big deal, I can keep
the two as separate workbooks and run the appropriate one, but if I can
somehow set the wrap attribute on the fly as it were, I can combine the
two
into one workbook.

Thanks!
Shawn