Solved

Quick concatenate formula request

Posted on 2015-01-22
9
66 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:Pete Edwards
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40564628
see attached example.

UDF
concatenate.xlsm
0
 

Author Comment

by:Pete Edwards
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 26

Expert Comment

by:ProfessorJimJam
ID: 40564697
see attached. simply you could replace the "I" with "|"
concatenate.xlsm
0
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 

Author Comment

by:Pete Edwards
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
 

Author Comment

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

thx
0
 
LVL 26

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:Pete Edwards
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 26

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:Pete Edwards
ID: 40566644
Right on!

That did the trick.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn various types of data validation for different data types in Excel 2013.

617 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