CONCATENATE
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, |
CONCATENATE
On Sep 10, 3:03*pm, wrote:
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 |
CONCATENATE
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? " wrote: 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, |
CONCATENATE
On Sep 10, 3:33*pm, art wrote:
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? " wrote: 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, 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 |
CONCATENATE
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, On Sep 10, 12:33*pm, art wrote: 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? " wrote: 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,- Hide quoted text - - Show quoted text - |
CONCATENATE
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 -- Gary wrote in message ... 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, |
CONCATENATE
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&A Q5&AR5&AS5 &AT5&AU5&AV5&AW5&AX5&AY5&AZ5&BA5&BB5&BC5&BD5&BE5&B F5&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" *γγραψε: 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 -- Gary wrote in message ... 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, |
All times are GMT +1. The time now is 04:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com