Need macro to format a list of numbers into a single string, separated by a semicolon

[Jeffrey Green]

we can search a web portal for a specific part or multiple parts.

however, to search for multiple parts, we have to enter like so;

abcdfe;syajkaiaioaoa;sdjhsjsiioa; etc.

What we want to search for, with a semicolon delimiter.

i would love a macro that would take the values in a column, put a ; after each one, then combine them into one cell.

so;

agajsjsus
jksjqiqklqlq
kdskwiqq
slowo
becomes

agajsjsus;jksjqiqklqlq;kdskwiqq;slowo

I doubt we would ever have a list so long that we bump up against the 32,000 character limit for a cell.

I guess i can either select the cells in the column, or if it is easier, i can select the entire column. Whatever is easier.

Thanks

 

[L. Howard]

Here are two options 'from the net'...

The first one is set up to have the column list in column A row 1 and on down, and the result will be in cell B1.

The second one you will follow the input box prompts to select the column list and then select the output cell.

Howard

Option Explicit

Sub generatecsv()
      Dim dataRow As Integer
      Dim listRow As Integer
      Dim data As String

      dataRow = 1: Rem the row that it is being read from column A otherwise known as 1 in vb script
      listRow = 1: Rem the row in column B that is getting written

      Do Until Cells(dataRow, 1).Value = "" And Cells(dataRow + 1, 1).Value = ""

        If (data = "") Then

          data = Cells(dataRow, 1).Value

        Else

          If Cells(dataRow, 1).Value <> "" Then

            data = data & ";" & Cells(dataRow, 1).Value

          Else

            Cells(listRow, 2).Value = data
            data = ""
            listRow = listRow + 1

          End If

        End If

        dataRow = dataRow + 1

      Loop

      Cells(listRow, 2).Value = data

End Sub



    
Sub ChangeRange()
'/"KutoolsforExcel"

Dim rng As Range
Dim InputRng As Range, OutRng As Range
Dim outStr

Set InputRng = Application.Selection

Set InputRng = Application.InputBox("Select your column list:", , InputRng.Address, Type:=8)

Set OutRng = Application.InputBox("Click on the Out-Put cell- (single cell):", , Type:=8)

outStr = ""

For Each rng In InputRng

    If outStr = "" Then
        outStr = rng.Value

    Else
        outStr = outStr & ";" & rng.Value

    End If

Next

OutRng.Value = outStr
End Sub

 

[Peter_SSs]

I doubt we would ever have a list so long that we bump up against the 32,000 character limit for a cell.

You would hope not.

If you have Office 365 then perhaps you don't need a macro at all, but use the formula shown in B1 below.

If you do not have access to the TEXTJOIN function then you could try this user-defined function and use it on the sheet like cell B2. The udf assumes a single-column range with no blank cells. If you want to pursue that further and you do/may have blanks, post back for a modification.

Function myConcat(r As Range, Optional Joiner As String = ";") As String
  myConcat = Join(Application.Transpose(r.Value), ";")
End Function

Excel Workbook
	A	B
1	agajsjsus	agajsjsus;jksjqiqklqlq;kdskwiqq;slowo
2	jksjqiqklqlq	agajsjsus;jksjqiqklqlq;kdskwiqq;slowo
3	kdskwiqq
4	slowo
Sheet1