Bruce Gust
asked on
How to combine text in Excel
In the attached screenshot, you see my attempts at combining the text assembled in column "E" - the fifth column over from the left.
What I want to do is craft an "forumula" that combines all of the text in column E so when I combine it with "CREATE TABLE 'volte7' (..." I'll have "CREATE TABLE 'volte7' ('EndDate' DATE NOT NULL, 'Num Days' varchar(20) DEFAULT NULL, 'Area' varchar(150) DEFAULT NULL...
...and so on.
You can see where I started to write out in column G "=E4&""&E5&" and I stopped because I was wondering if there was a quicker way to do it.
Thoughts?
What I want to do is craft an "forumula" that combines all of the text in column E so when I combine it with "CREATE TABLE 'volte7' (..." I'll have "CREATE TABLE 'volte7' ('EndDate' DATE NOT NULL, 'Num Days' varchar(20) DEFAULT NULL, 'Area' varchar(150) DEFAULT NULL...
...and so on.
You can see where I started to write out in column G "=E4&""&E5&" and I stopped because I was wondering if there was a quicker way to do it.
Thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Simon, that did it!
Thanks!
Thanks!
Consider using a user-defined function (UDF) to build your string with a worksheet formula like:
=StringBuilder(E4:E28)
To create a UDF:
1. ALT + F11 to open the VBA Editor
2. Insert...Module to create a blank module sheeet
3. Paste the following code there:
5. Use the function wizard (fx icon to left of formula bar) the first time you need to use the new function. It will be found under category "User defined".
StringBuilderQ28582044.xlsm
=StringBuilder(E4:E28)
To create a UDF:
1. ALT + F11 to open the VBA Editor
2. Insert...Module to create a blank module sheeet
3. Paste the following code there:
Function StringBuilder(rg As Range) As String
Dim s As String
Dim cel As Range
For Each cel In rg.Cells
If cel.Value <> "" Then s = s & cel.Value
Next
StringBuilder = s
End Function
4. ALT + F11 to return to the worksheet userinterface5. Use the function wizard (fx icon to left of formula bar) the first time you need to use the new function. It will be found under category "User defined".
StringBuilderQ28582044.xlsm
Glad to help. If you do EXACTLY the same thing a lot, it would be worth having a UDF for it, but for one-off quickies, I find formulae like the ones I posted the fastest way to achieve the desired result.
you could write VBA that iterates through each row and does the same thing. Were you trying to stay away from VBA?