Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate | Excel Discussion (Misc queries) | |||
Concatenate help | Excel Worksheet Functions | |||
Concatenate | Excel Programming | |||
Concatenate | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |