PDA

View Full Version : Excel Formula



LOSS1574
10-23-2008, 01:19 PM
Howdy:

I need some help trying to get the Service Type 1 column to match the desired output (last column on the right) in which is more cosmetically pleasing in the attached excel file.

The below formula is how I obtained the current Service Type 1 output:

=K2&"-"&L2&"-"&M2&"-"&N2&"-"&O2&"-"&P2&"-"&Q2&"-"&R2&"-"&S2&"-"&T2&"-"&U2&"-"&V2&"-"&W2

Copied Down to the last row of data.

Thank you,

Bob Phillips
10-23-2008, 01:52 PM
=LEFT(IF(A2<>"",A2&"-","")&IF(B2<>"",B2&"-","")&IF(C2<>"",C2&"-","")&IF(D2<>"",D2&"-","")&IF(E2<>"",E2&"-","")&
IF(F2<>"",F2&"-","")&IF(G2<>"",G2&"-","")&IF(H2<>"",H2&"-","")&IF(I2<>"",I2&"-","")&IF(J2<>"",J2&"-","")&IF(H2<>"",K2&"-","")&
IF(L2<>"",L2&"-","")&IF(M2<>"",M2,""),COUNTA(A2:M2)*2)

p45cal
10-23-2008, 01:58 PM
=SUBSTITUTE(TRIM(CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2," ",F2," ",G2," ",H2," ",I2," ",J2," ",K2," ",L2," ",M2))," ","-")

david000
10-23-2008, 09:56 PM
Sub Join_Them()
Dim arr As Variant, i, r As Integer, cel As Range, msg As String
Dim rng As Range

Set rng = Range("a2").Resize(, 13)

r = 0
Do
For Each cel In rng.Offset(r)
arr = Split(cel.Value)
For i = 0 To UBound(arr)
msg = msg & arr(i) & "-"
Next i
Next cel

On Error Resume Next
Range("N2").Offset(r) = Left(msg, Len(msg) - 1)

r = r + 1
msg = Empty
Loop Until r = 29
End Sub



And,
Chip Pearson has a custom function for this situation.
http://www.cpearson.com/excel/stringconcatenation.aspx

LOSS1574
10-24-2008, 06:37 AM
Thank you. You'll were a gigantic help:hi:

Bob Phillips
10-24-2008, 09:08 AM
Whilst the solution by p45cal is cute, I think you should go with a UDF.

This allows for any number of ranges, any length (within reason), such as

=Concat("-",B3:M3,T3:V3,Z3)



Function Concat(Delim As String, ParamArray inCells())
Dim res As String
Dim cell As Range
Dim i As Long

For i = LBound(inCells) To UBound(inCells)

For Each cell In inCells(i)

If cell.Value <> "" Then _
res = res & cell.Value & Delim
Next cell
Next i

Concat = Left$(res, Len(res) - 1)

End Function

Paul_Hossler
10-24-2008, 09:39 AM
With a bit of additional code, you can allow a mix of literals and the cells from multiple ranges.

I also added CStr() since I put some #DIV/0 in the ranges and the UDF didn't like that

Seems like a max of 28 is a strange limit for ParamArray()

=Concat2("-",A1:C1,"B", E1:G1,"A","B", "C","A","B", "C","A","B", "C","A","B", "C","A","B", "C","A","B", "C","A","B", "C","A","B", "C","A")



Function Concat2(Delim As String, ParamArray inCells())
Dim res As String
Dim cell As Range
Dim i As Long

'max of 28 (0 - 27) in ParamArray
For i = LBound(inCells) To Application.WorksheetFunction.Min(UBound(inCells), 27)

If TypeOf inCells(i) Is Range Then
For Each cell In inCells(i)
If CStr(cell.Value) <> "" Then _
res = res & CStr(cell.Value) & Delim
Next cell
Else
res = res & CStr(inCells(i)) & Delim
End If
Next i

Concat2 = Left$(res, Len(res) - 1)

End Function


Paul

Bob Phillips
10-24-2008, 09:58 AM
I changed it some more to show the error text, and to handles array constants

=concat3("-",A3:E3,"XYZ",{"A",1,2})



Function Concat3(Delim As String, ParamArray inCells())
Dim res As String
Dim cell As Range
Dim i As Long, j As Long

For i = LBound(inCells) To UBound(inCells)

If TypeOf inCells(i) Is Range Then
For Each cell In inCells(i)
If IsError(cell.Value) Then
res = res & CStr(cell.Text) & Delim
Else
res = res & CStr(cell.Value) & Delim
End If
Next cell
ElseIf TypeName(inCells(i)) = "Variant()" Then
For j = LBound(inCells(i)) To UBound(inCells(i))
res = res & CStr(inCells(i)(j)) & Delim
Next j
Else
res = res & CStr(inCells(i)) & Delim
End If
Next i

Concat3 = Left$(res, Len(res) - 1)

End Function

Paul_Hossler
10-25-2008, 06:21 AM
Even better

My Latin is a little rusty: What's the translation for "Nihil simul inventum est et perfectum" ?

I can guess at some of the words, but failed at making a sentence.

Paul

mdmackillop
10-25-2008, 06:40 AM
Even better

My Latin is a little rusty: What's the translation for "Nihil simul inventum est et perfectum" ?

I can guess at some of the words, but failed at making a sentence.

Paul
Paste it into a search engine.

Bob Phillips
10-25-2008, 06:40 AM
I wonder if the OP is still watching :dunno

Nothing is invented and perfected (simultaneously) at the same time.

Bob Phillips
10-25-2008, 06:41 AM
Paste it into a search engine.

If you do, you get loads of links to VBAX <g>