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>
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.