Solved

Quick concatenate formula request

Posted on 2015-01-22
9
60 Views
Last Modified: 2015-01-23
How can I quickly concatenate data, using the formula approach when needing over 800 individual cells is quite time consuming.
concatenate.xlsx
0
Comment
Question by:pedwards3
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40564628
see attached example.

UDF
concatenate.xlsm
0
 

Author Comment

by:pedwards3
ID: 40564644
looks like that may do the trick, but I need a |, not an I, and when I tried to implement, it did not work.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40564697
see attached. simply you could replace the "I" with "|"
concatenate.xlsm
0
 

Author Comment

by:pedwards3
ID: 40564759
must not work in my version of excel, keep getting #NAME? when applying the | instead of the I.  I even tried typing the =TextString in another cell and my excel is not recognizing this formula.  Any other options.  If you could just apply it to the row 5, instead of row 7, I'll just use yours.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:pedwards3
ID: 40564861
can anybody else help me, this is a time sensitive request.

thx
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40565133
you need to have the UDF in your excel module.  if you do not know how to put UDF,
follow the navigation
Open the Visual Basic Editor by pressing Alt+F11.
Click Insert then Module to insert a new module paste the below code in there

then in the workbook that you just pasted the below code in module. then in any of the cells type =TextString(select all ranges here then comma, then "|"
like this =TextString(A7:PQ7,"|")

then enter and it will work.
Option Explicit
Function TextString(TextRange As Variant, Optional Separate As String = " ") As String
Dim TextA() As Variant, NumCells As Long, NumRows As Long, i As Long, Orient As Long

    Orient = 0
    NumCells = TextRange.Columns.Count
    If NumCells = 1 Then
        NumCells = TextRange.Rows.Count
        Orient = 1
    End If

    ReDim TextA(1 To 2, 1 To NumCells)
    If Orient = 0 Then
        For i = 1 To NumCells
            TextA(1, i) = TextRange(1, i).Value
            TextA(2, i) = TextRange(1, i).NumberFormat
        Next i
    Else
        For i = 1 To NumCells
            TextA(1, i) = TextRange(i, 1).Value
            TextA(2, i) = TextRange(i, 1).NumberFormat
        Next i
    End If


    If TextA(1, 1) <> "" Then
        TextString = Format(TextA(1, 1), TextA(2, 1))
    End If

    For i = 2 To NumCells
        If TextA(1, i) <> "" Then
            TextString = TextString & Separate & Format(TextA(1, i), TextA(2, i))
        End If
    Next i

End Function

Open in new window

0
 

Author Comment

by:pedwards3
ID: 40565205
Thanks for the update.  I think I followed the direction articulately.  I'm getting some of the info looking like this:

Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral|Ge0eral
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40565297
ok
now in this case. you are close to get it done.

select all of the cells that have data, then change the format type to Text and click ok and it will work perfectly.
0
 

Author Closing Comment

by:pedwards3
ID: 40566644
Right on!

That did the trick.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel 2010 VLOOKUP 4 22
Excel Dashboard w/ Slicers - IDEAS 9 77
Excel 2016 crashes with "Closer" add-in 3 105
Excel - Score or Rank 3 91
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now