CONCATENATE

M

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,
 
S

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
 
A

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?
 
S

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
 
M

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,
 
G

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
 
J

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" έγγÏαψε:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top