Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Quick concatenate formula request

Posted on 2015-01-22
9
Medium Priority
?
72 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
  • 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

972 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