CONCATENATE

[marc747]

Hi,
I have a file that from Column A to AD and Rows 5 to 98 has formulas
that is linked to a data in different worksheet, some cells are only
numbers and some have text and numbers and some are blank, I am trying
to "CONCATENATE" each row to a cell with a "/" in between but it
should leave out the blank cells.
I would appreciate for any help.
Thanks,

 

[sbitaxi]

Hi,
I have a file that from Column A to AD and Rows 5 to 98 has formulas
that is linked to a data in different worksheet, some cells are only
numbers and some have text and numbers and some are blank, I am trying
to "CONCATENATE" each row to a cell with a "/" in between but it
should leave out the blank cells.
I would appreciate for any help.
Thanks,

Insert a column before A, and use the following.
Untested, please try on a sample.

Sub ConcatCells ()
Dim MyCell as Range
Dim MyRng as Range

Set MyRng = Range("A:A")
For Each MyCell In Range(MyRng)
With Application
MyCell.Formula = RTrim(Join(.Transpose(.Transpose _
(MyCell.Resize(, 30))), "/"))
MyCell.Replace What:="//", Replacement:="/", _
Lookat:=xlPart
End With
Next
End Sub

 

[art]

Try this and finish it for the rest of the row:

=CONCATENATE(IF(A1="","",A1),"/",IF(B1="","",B1),"/",IF(C1="","",C1),"/",IF(D1="","",D1),"/",IF(E1="","",E1))

Is this what you meant?

 

[sbitaxi]

Try this and finish it for the rest of the row:

=CONCATENATE(IF(A1="","",A1),"/",IF(B1="","",B1),"/",IF(C1="","",C1),"/",IF (D1="","",D1),"/",IF(E1="","",E1))

Is this what you meant?

Art, that will still leave him with a "//" for blank values.

Better -
=CONCATENATE(IF(A1="","",CONCATENATE(A1,"/")),IF(B1="","",CONCATENATE(B1,"/"))),IF(C1="","",CONCATENATE(C1,"/")))...
But Excel will not allow him to enter enough arguments for 30 columns
of data in this manner.


Steven

 

[marc747]

Yes, this is what I am looking for I tries it and works, but is there
any way to enter the "/" only when the cell is not blank. otherwise I
will end up having a lot of "//////".
Thanks,

 

[Gary Keramidas]

give this a try, just comment the msgbox line and uncomment the line after to
place it in the same row in column AE.
ia also used sheet1, so change the name if you need to.

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim lastcol As Long
Dim i As Long
Dim x As Long
Dim sStr As String
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

For x = 1 To lastrow
With ws
For i = 1 To lastcol
With .Cells(x, i)
If .Value <> "" Then
If sStr = "" Then
sStr = .Value
Else
sStr = sStr & "/" & .Value
End If
End If
End With
Next
End With
MsgBox sStr
' ws.Range("AE" & x).Value = sStr
sStr = ""
Next
End Sub

 

[John_John]

Hi all!

....this is not so elegand, but works correctly!

Follow the 8 steps above:

1
Type: =(A5&REPT("/";AND(LEN(A5)))) in cell "AE5".
2
Fill right the next 30 columns.
3
Fill down the rows you want.
4
Type:
=AE5&AF5&AG5&AH5&AI5&AJ5&AK5&AL5&AM5&AN5&AO5&AP5&AQ5&AR5&AS5
&AT5&AU5&AV5&AW5&AX5&AY5&AZ5&BA5&BB5&BC5&BD5&BE5&BF5&BG5&BH5
in "BI5".
5
Fill down.
6
Type: =IF(LEN(BI5);LEFT(BI5;LEN(BI5)-1);"") in "BJ5"
7
Fill down.
8
Hide columns "AE:BI"

Sorry for my English
John


Ο χÏήστης "Gary Keramidas" έγγÏαψε: