# Excel function to combine Concatenate and Trim



## DeputyStankus (Jan 18, 2008)

Most databases I work with have not been normalized, so I spend a lot of time trying to create my own unique identifiers. Normally, my formula looks like: =contatenate(trim(a1),trim(a2),trim(a3),...)

Is there one function I can use instead? e.g.: =???(a1,a2,a3)


----------



## slurpee55 (Oct 20, 2004)

=TRIM(A1&A2&A3&A4&A5&A6...) gives the same result


----------



## DeputyStankus (Jan 18, 2008)

Not quite what I'm looking for (I'd like to use as few keystrokes as possible). Thanks for the help, though.


----------



## cristobal03 (Aug 5, 2005)

A built-in Excel function? I don't know. And I don't know if VBA has an analog for this, but most languages have the opposite of a *split* routine, so if your members are in an array you could write an iterative control to handle the trim and then array_to_string (PHP, I know ) it.

*shrug* just thinking outside the box a bit. But I'm not very familiar with the Excel object model. There are gurus galore around here though. Speaking of which, welcome to TSG :up:

chris.


----------



## slurpee55 (Oct 20, 2004)

DeputyStankus said:


> Most databases I work with have not been normalized, so I spend a lot of time trying to create my own unique identifiers. Normally, my formula looks like: =contatenate(trim(a1),trim(a2),trim(a3),...)
> 
> Is there one function I can use instead? e.g.: =???(a1,a2,a3)


Well, =TRIM(A1&A2&A3&A4&A5&A6...) is the same as =TRIM(CONCATENATE(a1,a2,a3,...), but that's the best you can do with a formula. There may be a way to code it, but even then you will have to enter the cells, unless you have regions, like a1:a3 - and you can't use that in a concatenate formula, I am afraid. I guess even Excel has limits....


----------



## Zack Barresse (Jul 25, 2004)

You could write your own UDF (User Defined Function) via VBA, or you could download the Morefunc.xll add-in by Laurent Longre and make use of the MCONCAT() function, so your function would look like =MCONCAT(A1:A6)

You can select a delimiter also if you'd like. There is documentation with the add-in. Look here for the website: http://xcell05.free.fr/english/

HTH


----------



## slurpee55 (Oct 20, 2004)

Cool - I thought you might come through with something, if it existed, firefytr....


----------

