Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Quick concatenate formula request

Posted on 2015-01-22
9
Medium Priority
?
68 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 27

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 27

Expert Comment

by:ProfessorJimJam
ID: 40564697
see attached. simply you could replace the "I" with "|"
concatenate.xlsm
0
[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

 

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 27

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 27

Accepted Solution

by:
ProfessorJimJam earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: 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…
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

715 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