Solved

Quick concatenate formula request

Posted on 2015-01-22
9
59 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Moving applications to the cloud or switching services to cloud-based ones, is a stressful job.  Here's how you can make it easier.
Use of TCL script on Cisco devices:  - create file and merge it with running configuration to apply configuration changes
Viewers will learn various types of data validation for different data types in Excel 2013.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

758 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

20 Experts available now in Live!

Get 1:1 Help Now