Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Concatenate range with commas Results

I have a range which is found by carrying out some custom filters.

Is there a piece of code to concatenate the cells found into one line with commas between them on another sheet?

e.g

In Sheet 1 After the filter Column A has the following data visible

A1 = Title
A5 = 1234
A9 = 4567
A15 = 4325
A300 = 9245

So Sheet 2 Range A1 would be

1234, 4567, 4325, 9245

This needs to be done 10 times to fill Range A1:A10 with data (each search is different).

Any help, as ever, greatly appreciated!

Hi All

I have a range of values (Example - Age) in a worksheet (wksht1) and in another worksheet i have a the list (Example Name and Age). I would like to use in a single Macro to do a VLOOKUP and get all the Names. Once i have the names I would like to concatenate them into a single cell separated with commas.

I have been struggling to solve this for a long time.
Pls help me

Thanks in advance

Hi All,

Here is my problem. I have to concatenate a row having values in some columns but some are blank. I would like to concatenate those values with commas ;or any other charachter that can work for text to columns later; excluding the blank cells. For example;
A____B_____C____D____E____F
aaa__bbb__ccc__ddd _______ffff

to aaa,bbb,ccc,ddd,fff in one cell. There are several rows in the sheet and the range is not static. After the macro runs i would like all rows to be concatenated.

It looks simple but i am quite new to this kind of things. I believe you can help me with this.

Kindest Regards,

I have a defined name range (let's just call it INCIDENT
it selects a range in column A of all incidents it finds
So let's say the defined range is A4:A7
looks like this:
92980
93879
94349
95287

I would like to concatenate based off the defined name range with a final result looking like this:
92980, 93879, 94349, 95287

note: this range can grow or shrink based off a query refresh.

The defined name INCIDENT is always correct after the refresh.
I just need to find a way of putting this range into one (1) cell with commas separating them.

Is this possible

Hi there,

I have run out of ideas on this one and now I've lost my fresh morning mind,
so any help would be much appreciated.

I have a list of garments and each style has a different number of sizes in
its size range from a minimum of 1 available size to a maximum of 24
available sizes, so I have 24 columns for sizes.

I want to concatenate the available sizes for each style separated by a
comma and a space.

A simplified version of the data as it currently stands is like this:

A B C D E etc.....out to 24 columns
1 Stock Item Name Available Sizes
2 Stock Item A 8 10 12 14
3 Stock Item B S M L
4 Stock Item C OS

and the result I want in one column (single cell) is:

A B
1 Stock Item Name Available Sizes
2 Stock Item A 8, 10, 12, 14
3 Stock Item B S, M, L
4 Stock Item C OS

The problem when I use concatenate or "&" across the 24 columns is that I
end up with commas and spaces after the available sizes for all of the
remaining blank cells.

I can't use IF because I can't embed that many IF statements in the one
formula.

Hope this makes sense. Thanks.

I apologize if this is too lengthy. I am using a User defined formula to
take a range of values in the workbook and concatenate them into one long
string value . I also want them to be separated by commas.

Example (real data is much larger):
A1: 011800
A2: 121801
A3: 051810
A4:
A5:

I would like to use a formula in B1 that would get this result: 011800,
121801, 051810

I have been able to accomplish this to an extent with the following user
defined formula:
Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

But I am getting this result: 011800, 121801, 051810, , ,

I get a comma for each blank cell. How can I get around this?

Steve

Hi All

I have a range of values (Example - Age) in a worksheet (wksht1) and in another worksheet i have a the list (Example Name and Age). I would like to use in a single Macro to do a VLOOKUP and get all the Names. Once i have the names I would like to concatenate them into a single cell separated with commas.

I have been struggling to solve this for a long time.
Pls help me

Thanks
Sp123

Hi,

Have got a CSV file of religious verse, with each verse in a new row, verses separated by a blank cell:

Verse 1
abcdef
abcdefg
abcdefgh

Verse 2
abcdef
abdefg
abcdefgh

I need to import it into a database for use by an Android app, but the CSV has to be in the format of:

Tag Value
1 a,b,c
2 d,e,f

and so on.

Is it possible for a concatenate VBA function that will concatenate the rows, using a comma delimiter, but when a blank cell is encountered, to create concatenate the subsequent cells into a new row, so that it looks as per the Tag/Value combination above please?

I've looked at a few Concatenate VBA functions on here but haven't got figured out how to do it yet beyond manual range selection!

Thanks in advance!

Hello ozgrid community,

I am having a bit of an issue making a macro that will save my file. Here is what I have:


	VB:
	
 Copy() 
     
    Const FileName As String = "C:test.txt" 
    Dim FileNo As Integer 
    Dim x As Long 
    FileNo = FreeFile 
     
     
    Open FileName For Output As #FileNo 
     
    With Sheets(8) 
        For x = 1 To .Range("A1:A" & .Range("A65536").End(xlUp).row).Count 
            Print #FileNo, .Cells(x, 1).Value 
        Next x 
    End With 
    Close #FileNo 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above code works just fine and the reason why I use a constant is because I want it to copy all the row values. If I use "xltext" then it will convert all my rows that have commas with a quote in the front of each row. The reason why I don't use "xltextPrinter" is because some of my rows are too long and when it gets converted incorrectly into a .txt document

The above code works fine but I want to add Date and Time to the saved file name instead of just test.txt
I tried this:


	VB:
	
 Copy() 
    Dim FilePath As String 
    Dim FileName As String 
     
    FileName = "HELP_" & Worksheets("Color").Range("A2").Value & "_" & _ 
    Format(Now, "mm-dd-yyyy") & "_" & Format(Now, "hhmmss") 'Concatenate the name of file with date and time that I need
     
    FilePath = "C:" 
     
    Const FileOutput As String = "FilePath" & "FileName" 
     
    Dim FileNo As Integer 
    Dim x As Long 
    FileNo = FreeFile 
     
     
    Open FileOutput For Output As #FileNo 
     
    With Sheets(8) 
        For x = 1 To .Range("A1:A" & .Range("A65536").End(xlUp).row).Count 
            Print #FileNo, .Cells(x, 1).Value 
        Next x 
    End With 
    Close #FileNo 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This above code doesn't work because I assume that you cannot concatenate constants. I just want it to save the cell range with date and time without the limitations of xltext and xltestprinter.

Any help would be amazing!

I am in need of concatenating up to 64 cells with a comma seperator.
Can I do this with a loop?
Here is portion of what I currently have, which is not sufficient.


	VB:
	
Range("B1").Select 
ActiveCell.FormulaR1C1 = _ 
"=Sheet1!RC&"",""&Sheet1!RC[1]&"",""&Sheet1!RC[2]&"",""&Sheet1!RC[3] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance.

Krishnakumar, the genius that he is, supplied me with this code and it works great to match account numbers in column A and totaling the adjoining balances in column B. This has saved so much time.

Now there are more columns of information I need to include in my data. For example, what salesman is associated with a particular account number, which would be in column C. I have tried to alter the code to include more columns of data with no luck. I think it is because the first two columns are 'concat' which I would need to do to the other columns. Am I correct in thinking this?

I was also asked if there was a way to show, on another sheet within the workbook what consists of the total. For example if John Doe has two accounts that made up the total in (the original sheet) column B pull the detail on an additional "detail" sheet.

I would really appreciate any help.

	VB:
	
 Test() 
    Dim sWs As Worksheet, dWs As Worksheet 
    Dim dRng As Range, Concat As Range 
    Dim lRow As Long 
     
    Set sWs = Sheets("Info") 
    Set dWs = Sheets("Totals") 
    lRow = sWs.Range("A" & Rows.Count).End(xlUp).Row 
    Set Concat = sWs.Range("D2:D" & lRow) 
    Set dRng = dWs.[a1] 
     
    dRng.CurrentRegion.ClearContents 
    With Concat 
        .Formula = "=TRIM(A2&""#""&B2)" 
        .Value = .Value 
    End With 
    sWs.[d1] = "Concat" 
    With Concat 
        .Offset(-1).Resize(lRow, 1).AdvancedFilter action:=xlFilterCopy, _ 
        copytorange:=dRng, unique:=True 
    End With 
    With dWs.Range("C2:C" & dWs.[a65536].End(xlUp).Row) 
        .Formula = "=sumif('" & sWs.Name & "'!d$2:d$" & lRow & ",a2,'" _ 
        & sWs.Name & "'!c$2:c$" & lRow & ")" 
        .Value = .Value 
        .NumberFormat = "$#,###.0" 
    End With 
    With dWs.Range("A2:A" & dWs.[a65536].End(xlUp).Row) 
        .TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _ 
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ 
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ 
        :="#", FieldInfo:=Array(Array(1, 1), Array(2, 1)) 
    End With 
    dWs.[a1] = "Tax ID#": dWs.[b1] = "Name": dWs.[c1] = "Balance" 
    sWs.Columns(4).Clear 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have the following codes i would like to combine to run together, i want the first one to add preceding zeroes then after that concatenate them into one cell, with comma delimiters added. Each number needs to separate by single quotes and a comma IE '0123456', '0123456', etc.... And preferably having an input text box come up for me to input what to encapsulate the numbers in (the single quotes and comma). So basically it would loop from the second cell down to the end until all the numbers were encapsulated.


	VB:
	
 Macro3() 
     'Declarations
    Dim i As Integer, j As Integer, endrow As Long 
     'Converts the A column format to Text format
    Application.ScreenUpdating = False 
    Columns("A:A").Select 
    Selection.NumberFormat = "@" 
     'finds the bottom most row
    endrow = ActiveSheet.Range("A1").End(xlDown).Row 
     'selects the top cell in column A
    ActiveSheet.Range("A1").Select 
     'loop to move from cell to cell
    For i = 1 To endrow 
         'Moves the cell down 1. Assumes there's a header row so really starts at row 2
        ActiveCell.Offset(1, 0).Select 
         'The Do-While loop keeps adding zeroes to the front of the cell value until it hits a length of 7
        Do While Len(ActiveCell.Value) < 7 
            ActiveCell.Value = "0" & ActiveCell.Value 
        Loop 
    Next i 
    Application.ScreenUpdating = True 
End Sub 
CODE 2 
Sub Concatenate() 
    Dim w As Long 
    Dim t As String 
    Dim Rng As Range, c As Range 
    w = 1 
    Set Rng = Selection 
    For Each c In Rng 
        If c  "" Then 
            t = t & c & " " 
        Else 
            w = w + 1 
            t = Left(t, Len(t) - 1) 
            Cells(w, 3).Value = t 
            t = "" 
        End If 
    Next c 
    w = w + 1 
    t = Left(t, Len(t) - 1) 
    Cells(w, 3).Value = t 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a range of cells B40:AB40 that have various numbers from 000 through 099.

Some of the cells at the end are empty depending on the input.

I am trying to concatenate only the cells with numbers in this range.

1. Concatenate the range

2. Keep the text structure "000"

3. Separate by commas

Here is a UDF I found:

Function Concat(myRange As Range, Optional myDelimiter As String)
Dim r As Range

Application.Volatile
For Each r In myRange
If Len(r.Text) > 0 Then
Concat = Concat & r & myDelimiter
End If
Next r
If Len(myDelimiter) > 0 Then
Concat = Left(Concat, Len(Concat) - Len(myDelimiter))
End If
End Function

It does everything I want but it will not keep the text format as "000".

For example:

This below:

004 007 008 010 011 020 050 055 080

With the Function above:

4,7,8,10,11,20,50,55,80

It should be: (in one cell)

004,007,008,010,011,020,050,055,080

Thank you in advance!!

Hi Duke,
Thanks a bunch.
Mary

"Duke Carey" wrote:

> try
> =text(A1,"#,##0")&" - "&text(B1,"#,##0")
>
> "Mary" wrote:
>
> > I have two number columns with #,### formats, I need to concatenate the
> > columns and display as a range without loosing the format
> > eg
> > col A col B col C ( Range)
> > 1,000 1,500 1,000 - 1,500
> >
> > when I use the concatenate function, I lose the comma format of the numbers.
> > so, it comes as 1000 - 1500 instead of 1,000-1,500.
> > Can someone help me????
> >
> >

Hello all -

Here is what I am wanting to do: I have a spreadsheet that has X number
of cells populated in column A. I want to write a macro that will do the
following: concatenate all populated cells in column A into one cell,
separated by a comma (with no space).

Column A
A1
A2
A3

Becomes
A1,A2,A3

The cells in column A may vary depending on a given spreadsheet. I want
to be able to use the macro on any spreadsheet without having to tweak
the cell range manually.

Thanks!

Tom

--
TJM
------------------------------------------------------------------------
TJM's Profile: http://www.excelforum.com/member.php...o&userid=12746
View this thread: http://www.excelforum.com/showthread...hreadid=378139

I am converting some reports to excel. I have been trying to code a macro to do the job. My problem comes with names. Some records have middle names/initials, and some do not. So text to columns leaves "lastname,firstname" in column C, and middlename for some rows in a distribution date column (D). I would like the full names (including middle) in column C.

My tactic was to sort the data by the distribution date column, search for the first non-date, select a range from the active cell to the end of the column, then insert cells in the column to the right to concatenate the last,first and middle names.

1 Smith,John 1/18/2011 12/1/2010 50
2 Jones,Bob Allen 1/18/2011 12/2/2010 100

If I hard code the active cell like this


	VB:
	
ActiveSheet.Range("D8", ActiveSheet.Range("D8").End(xlDown)).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it works fine. However, when I use the code below I get an error message.


	VB:
	
ActiveSheet.Range(ActiveCell, _ 
ActiveSheet.Range(ActiveCell).End(xlDown)).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The following works, but leaves the messy cleanup of columns of zeroes after the rest of the code compiles.


	VB:
	
ActiveSheet.Range(ActiveCell, "D65536").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

It seems like there's something simple I'm missing. Below is my code for dealing with the middle names. I've also included a sample excel file with the types of names I see.MiddleNameSheet.xls

	VB:
	
 MiddleName() 
     
     'Find first non-date cell in DistDate column
    Range("D8").Select 
    While IsDate(ActiveCell) 
        Selection.Offset(1, 0).Select 
    Wend 
     
     'Insert cells adjacent right to middle names and fill cells with concatenated names.
    Selection.Offset(0, 1).Select 
    ActiveSheet.Range(ActiveCell, _ 
    ActiveSheet.Range(ActiveCell).End(xlDown)).Select 
    Selection.Insert Shift:=xlToRight 
    ActiveCell.FormulaR1C1 = "=RC[-2] & "" "" & RC[-1]" 
    ActiveSheet.Range(ActiveCell, _ 
    ActiveSheet.Range(ActiveCell).End(xlDown)).Select 
    Selection.FillDown 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
     'Delete old name and middle name cells.  Insert space after comma in all names.
    Selection.Offset(0, -1).Delete 
    Selection.Offset(0, -2).Delete 
    ActiveSheet.Range("C8", ActiveSheet.Range("c8").End(xlDown)).Select 
    Selection.Replace What:=",", Replacement:=", ", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Can anyone tell me how to do a mass concatenation while inserting values in between existing values?

For example, if i have the five cells below (read the space as a cell):

120
142

135
136

How can i return the following results - 120,142,135,136

The keys are as follows...
1. Insert commas between values
2. skip both the cell value and comma when a cell is blank
3. I can't do individual cells references as i'm dealing with many, many cells, so it's must be a range based function (think of doing as above but with 100 cells with blanks interspersed periodically).

i'm currently using Excel 2003.

I have a list of 100 items of data

I want to be able to go down the list of items and select the ones that I want.

I then want to concatenate the items with a “, “ (comma and a space) between each one

I never know how many I will be selecting, it could range from 0 – 30 of the items.

I also need the order of the items concatenated in a specific order.

Let’s say the items are in column B from rows 1 - 100

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >*</td><td >Item 1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >*</td><td >Item 2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >*</td><td >Item 3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >Item 4</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >*</td><td >Item 5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >*</td><td >Item 6</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >*</td><td >Item 7</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >*</td><td >Item 8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >*</td><td >Item 9</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >*</td><td >Item 10</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >*</td><td >Item 11</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >*</td><td >Item 12</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

And so on.

Now I want to select 3 items out of the list Item 1, Item 3 and Item 5. So I go down the list and put a number in column "A" to correspond to the order I want the items concatenated in

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">3</td><td >Item 1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >*</td><td >Item 2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td><td >Item 3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >Item 4</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2</td><td >Item 5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >*</td><td >Item 6</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >*</td><td >Item 7</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >*</td><td >Item 8</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >*</td><td >Item 9</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >*</td><td >Item 10</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >*</td><td >Item 11</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >*</td><td >Item 12</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

Now I want to concatenate the items I need like this:
Item 3, Item 5, and Item 1

I am using VLOOKUP function to extract and order the items But the problem I have is I never know how many if any items I will be selecting, or the order in which they are concatenated. So I created 30 VLOOKUP functions like this on another sheet and I get the data back like this:

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&#160;</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td >Item 3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td >Item 5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td >Item1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">5</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">7</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">8</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">9</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">10</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">11</td><td >#N/A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">12</td><td >#N/A</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web &#62;&#62; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

Now I have the items I need and in the order I want along with the rest of the #N/A's.

Because I never know how many items will be selected from 0 to 30, how do I create a formula that will just concatenate the items I need? Or is there a better way of doing this?

I have two number columns with #,### formats, I need to concatenate the
columns and display as a range without loosing the format
eg
col A col B col C ( Range)
1,000 1,500 1,000 - 1,500

when I use the concatenate function, I lose the comma format of the numbers.
so, it comes as 1000 - 1500 instead of 1,000-1,500.
Can someone help me????

Hello. I am trying to create a macro that will create a comma and space between every 5th character within a cell. I have been unable to get very far at all with that idea, although if anybody can help, that would be preference #1.

What I decided to go with for now, instead, is to try (using the macro recorder) text to columns, fixed width, and do this after every 5th character, and selecting each cell to be formatted as text.

What I am having difficulties with is trying to tell the macro which cells to touch and which ones not to. I do not want the macro to do text to columns and fixed width on simple text. I only want it to do the macro when it is a string of numbers that, during transfer from PDF to excel, have been concatenated into one cell. I also want the macro to do this for infinite number of rows. I have excel 2007.

I am attaching a small excel file with examples of data I am working with. I have saved this excel file as a 97-2003 version for those who do not have 2007.

As I stated, if anybody can help me figure out how to do a simple insert comma space every 5th character where information has been concatenated, that would be preference. I will gladly take any help at all, though.

Also, I am not trying to change anything within column A. Only column B. On the sample file please note B3 - B6. Rows 5 & 6 are fine as they are. Other problem rows include B11 - B13

Thank you!

Sub CommaSeparate5thCharacter()
'
' CommaSeparate5thCharacter Macro
'

'
    Application.ScreenUpdating = False
    
    
    Columns("B:B").Select

    Range("B1", Range("B1").End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("B1", Range("B1").End(xlDown)), DataType:=xlFixedWidth _
        , FieldInfo:=Array(Array(0, 2), Array(5, 2), Array(10, 2), Array(15, 2)), TrailingMinusNumbers _
        :=True
    Range("B1", Range("B1").End(xlDown)).Select
    
    Application.ScreenUpdating = True
    
End Sub



No luck finding an answer? You could always try Google.