Concatenate #2

I have four columns to combine and I use the concatenate function.  One of my 
columns has either 3,4 or 5 characters.  When I concatenate, there are spaces 
in the middle of the end product.

How do I get those spaces to disappear in column 1?  I can't just set the 
length of the column to 3 or 4 or 5 characters because I need that varied 
length.  I just don't want the spaces at the end.
0
susie (64)
2/13/2008 12:23:02 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
578 Views

Similar Articles

[PageSpeed] 7

With
A1: "Alpha "
B1: "Bravo "
C1: "Charlie "
(all without the quotes, of course)

This formula scrubs the spaces from the end result:
D1: =SUBSTITUTE(A1&B1&C1," ","")

In that example, it returns "AlphaBravoCharlie"

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Susie" <Susie@discussions.microsoft.com> wrote in message 
news:68806B8B-2176-4630-9F3F-E445D35E916A@microsoft.com...
>I have four columns to combine and I use the concatenate function.  One of 
>my
> columns has either 3,4 or 5 characters.  When I concatenate, there are 
> spaces
> in the middle of the end product.
>
> How do I get those spaces to disappear in column 1?  I can't just set the
> length of the column to 3 or 4 or 5 characters because I need that varied
> length.  I just don't want the spaces at the end. 


0
2/13/2008 12:49:01 AM
=TRIM(B1&C1&D1&E1)  entered in A1


Gord Dibben  MS Excel MVP

On Tue, 12 Feb 2008 16:23:02 -0800, Susie <Susie@discussions.microsoft.com>
wrote:

>I have four columns to combine and I use the concatenate function.  One of my 
>columns has either 3,4 or 5 characters.  When I concatenate, there are spaces 
>in the middle of the end product.
>
>How do I get those spaces to disappear in column 1?  I can't just set the 
>length of the column to 3 or 4 or 5 characters because I need that varied 
>length.  I just don't want the spaces at the end.

0
Gord
2/13/2008 12:53:09 AM
Not considered good to post in muliple groups

Tyro

"Susie" <Susie@discussions.microsoft.com> wrote in message 
news:68806B8B-2176-4630-9F3F-E445D35E916A@microsoft.com...
>I have four columns to combine and I use the concatenate function.  One of 
>my
> columns has either 3,4 or 5 characters.  When I concatenate, there are 
> spaces
> in the middle of the end product.
>
> How do I get those spaces to disappear in column 1?  I can't just set the
> length of the column to 3 or 4 or 5 characters because I need that varied
> length.  I just don't want the spaces at the end. 


0
Tyro (331)
2/13/2008 1:56:21 AM
Reply:

Similar Artilces:

Can someone help me??? Try #2
Let me start off by saying that I am no where near an excel expert so please bear with me. I have a schedule for my employees which is attatched to this post as an html file. They are contained in three different sheets within a workbook. Each sheet has its own formulas and such but I have excluded them and also condensed them down to save repitition. The 3 scheduling sheets are named Servers, Hostess, and BarBus I also have/want 7 other sheets named Mon thru Sun and a template is attatched as an html file as well. What I need to get working is the Mon thru Sun sheets to automatically pu...

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

Modal on Modeless #2
I have created several modeless forms for my app, all with the desktop set as parent by using dlg.create(IDD_DLG,GetDesktopWindow()). I did this because I want the user to be able to have each window open, only allow one instance of each window, and allow the user to switch between windows and the main form easily by using taskbar icons. I would like to have a button on one of these modeless windows that will pop up a modal window, that is only modal to that modeless window, so that they can not go back without closing the modal window, though they can still go to any of the other modeless...

Exchange 2007 Evaluation version #2
Can I install it on 64 bit platform and run it as production? On Fri, 23 Feb 2007 09:55:13 -0800, RG <RG@discussions.microsoft.com> wrote: >Can I install it on 64 bit platform and run it as production? There isn't an evaluation version as such. You can install Exchange 2007 without a key for 120 days. ...

worker thread #2
I want to run function in a worker thread: AfxBeginThread(MyDlg::Draw, NULL); Function Draw is declared as protected class member function: UINT Draw( LPVOID pParam ); I have an error during compiling: error C2665: 'AfxBeginThread' : none of the 2 overloads can convert parameter 1 from type 'unsigned int (void *)' What can I run Draw function in a worker thread? ArtuS "ArtuS" <aszeja@gazeta.pl> wrote in message news:c9i826$b9d$1@inews.gazeta.pl... > I want to run function in a worker thread: > AfxBeginThread(MyDlg::Draw, NULL); > > Function Dr...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Xml2PDF formatter version 2.2 is released
Xml2PDF formatter version 2.2 March 30, 2005 Altsoft N.V. Altsoft N.V. announces the release of version 2.2 of its Xml2PDF formatting engine. New XSL-FO features are: - complete support for Unicode BiDi algorithm; - support for soft-hyphen based hyphenation; - improved performance and memory usage; - support for PDF and EPS as external graphics formats; - added control over maximal images resolution (all images above a fixed resolution are downsampled); New SVG features are: - support for markers; - support for symbols; - improved performance and memory usage; New XHTML features a...

Convert text to numbers #2
I have a large spreadsheet with a column that has the numbers formatted as text. I tried reformatting the column from numbers to text but when I sum the column with other columns (that are formatted as numbers) it still doesn't show in the sum. The spreadsheet is too big for me to retype the numbers. Can anyone please help??? In a another column insert a formula that multiplies the first colum that you are having problems with by 1. i.e = a1*1 and drag all the way down. This will force Excel to create a result that is a number. Then copy this new results column on top of the old one...

A program is trying to access your Address Book #2
Since downloading the SP3 patch for Outlook 2002, I'm getting messages that oblige me to manually elect to send messages or use names found in my Address Book. For example, "A program is trying to access your Address Book". Is there a way to elect out of getting these messages every time one emails? The program is allowing to grant permission for periods up to 10 minutes... but that is woefully inadequate. Thanks for your help. B Touhy Check the following for more information about the enhanced security model that protects Outlook. http://www.slipstick.com/outlook/esecup.h...

SQl Trigger #2
Does anyone have any documentation for creating a SQL Trigger? Here is a link to the MSDN site at Microsoft: http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax) "tommyvcfs" <tommyvcfs@discussions.microsoft.com> wrote in message news:2DDAF3DE-8B57-4DD0-9BA0-24DE6AE3119B@microsoft.com... > Does anyone have any documentation for creating a SQL Trigger? PERFECT! THANK YOU Tommyvcfs &q...

Printing List of Attachments #2
When I print out a sent email message (Outlook XP) it does not tell me on the printout what attachments were on the message. I don't want the attachments to print, just a list of them Any ideas? It used to! On Fri, 25 Feb 2005 15:53:14 -0000, "Keith" <@.> wrote: >When I print out a sent email message (Outlook XP) it does not tell me on >the printout what attachments were on the message. > >I don't want the attachments to print, just a list of them > >Any ideas? It used to! > Before printing the message, change its format to plain text an...

Memories of Lotus 1-2-3
Way back in the deep dark recesses of my memory I seem to recall some way of extracting all unique values of a certain field from a database in Lotus 1-2-3 to a different portion of the spreadsheet. I have a need to do the same thing now, but like most of the world, I am using Excel now. I can't seem to figure out how to get this done in Excel...in Lotus...I think it was with a data - extract command. Thanks for the help !! Put a label cell on top of the column containing the data Click on the label cell and then pulldown: Data > Filter > Advanced Filter... Check Copy to a...

Memory Usage #2
Hi, I have developed an application which monitors traffic around a city centre car park. The application works fine as intended and I have been testing it lately. One thing I have noticed which is starting to concern me is that when I start the application its memory usage is around 8.5M in task manager. If I leave it to run overnight, it rises to 18M. I have been careful of releasing everything I have allocated and I don't get no memory leaks reported in Visual Studio. Firstly, what things could be responsible for this increase in memory? Secondly, how can I detect and fix thes...

Sorting 2 lists
I'm sure there's a name for what I'm trying to do, but I don't know it so I can't search previous questions for answers. Please help! I have 2 lists: List 1, column A - Customer names List 1, column B - annual sales 2003 List 2, column C - Customer names (some of which are also on List 1) List 2, column D - annual sales 2004 I want to match up customers that exist in both lists and be able to compare 2003 sales in a column right next to 2004 sales. The customers that don't match up can be listed below. Hi, Am I correct that the two lists are on the same sheet...

linking #2
how do you link sheets together, like i have i the totals in 5 or 6 different sheets and on the last sheet i want the grand total. i need help!! Its pretty simple! You can simply write a formula which looks like this =SUM(Jan:Apr!D3) where, Jan, Feb, Mar and Apr are 4 sheets and i am writing the formula in another sheet named Total. If your data is exaly on the same location in all sheets, you can simply use this formula instead of saying =Jan!B4+Feb!B4+Mar!B4+Apr!B4 Hope this helps. ...

How to #2
Sorry but I am not familiar at all with Publisher. I have a client that needs to put an (*.jpeg) image onto an envelope as the Return address. Can someone familiar with the procedure inform me. Thank you -- Peter Please reply to Newsgroup for the benefit of others. File, Page setup, envelope (various sizes available), insert, picture... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Peter Foldes" <okf22@email.msn.com> wrote in message news:uDLJIzVMFHA.2252@TK2MSFTNGP15.phx.gbl... > Sorry but I am not famili...

upgrade #2
I recently purchased Money 2004, my previous version being Money 98. When I try to restore the backup an error message says that it can't convert the file because it is an incomatible version. What do I need to do to try to transfer all of my data from 98 to 2004? The mother of all FAQs: http://www.bollar.org/msmoney/#Q1. "Jodie" <anonymous@discussions.microsoft.com> wrote in message news:39c601c3e269$fb85f6d0$a601280a@phx.gbl... > I recently purchased Money 2004, my previous version > being Money 98. When I try to restore the backup an error > message says ...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

Reports #2
Hi, does RMS has a report, that will show every Item sold, layaway, work order. for specific day? Thanks -- Reynold Cycle Unsure myself if there is anything more specific than my suggestion, but as for the items sold use the "Item Movement" report and filter on todays date & Sales >0. You can also filter on the date on the Layaway & Work Order reports. Hope this is what you were looking for. -- Jeremy Janisch "Jose" wrote: > Hi, does RMS has a report, that will show every Item sold, layaway, work > order. for specific day? > Thanks > >...

PNG files #2
i read the discussion about the PNG files... I had the same prob. when i got Publisher 2002, but it was fixed by downloading the SP1 i think it was... but now my dad bought the Publisher 2003 - and i/we still have that stupid problem, and here its quite difficult to get rid of the very large PNG files - I dont know what to do - its VERY irritating that when you save your website - the size of it is nearly the same as the original document, because of the PNG's does the office 2003 SP1 - of 1.april 2004 solve the problem or not ??? nobody seems t know for real... The Service pack ad...

Passing DB connection object between 2 Process
Hi All, Is it possible to pass the DB connection object (CDatabase) to a different process, such as a COM+ object, rather than passing the user name and password and recreating the connection? Thanks Marco ahhhhh sounds tricky do let me know "Marco Hung" <marco.hungmarco@gmail.com> wrote in message news:%23pIvAPvSFHA.628@tk2msftngp13.phx.gbl... > Hi All, > Is it possible to pass the DB connection object (CDatabase) to a different > process, such as a COM+ object, rather than passing the user name and > password and recreating the connection? > > Thanks ...

ExcelReport 2.1 Released! #2
ExcelReport 2.1 Released! Please access http://www.ljzsoft.com ExcelReport is an Excel report generator that outputs reports in Microsoft Excel spreadsheet format. If you know how to use Microsoft Excel and write SQL statements, you can use ExcelReport to create all kinds of Excel reports as you need. ExcelReport can read an Excel report template file and an XRF file. The Excel report template file defines the layouts and formatting of a report. The XRF file has some configuration information and SQL statements, describes how to access database. First ExcelReport creates a blank Excel report ...

Error Messages #2
Hi I hope you can help... I am using vlookup functions and as we know if it can't find a value i returns a cell value of #N/A. However the result of the lookup is used to calculate another part o the worksheet. So if there is an error #N/A I want it to appear in th spreadsheet either as a blank cell or as a value of 0. Is this possible -- Message posted from http://www.ExcelForum.com Hi try =IF(ISNA(VLOOKUP(....),0,VLOOKUP(...)) HTH Frank > Hi I hope you can help... > > I am using vlookup functions and as we know if it can't find a value > it returns a cell value...

onChange event #2
This is a multi-part message in MIME format. ------=_NextPart_000_0063_01C5A702.CB9E9CB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I am currently trying to add 2 onchange events to our implementation. = The first onchange event should allow certain values of picklist B be = seen when a value of Picklist A is chosen. For example Picklist A =3D 1 , picklist B =3D 1.1, 1.2, 1.3 Picklist A =3D 2 , picklist B =3D 2.1, 2.2, 2.3 I have read the previous posts on here and have ended up with the = following jav...

Can anybody convert 2 small pub files for me?
Hi, is there anybody who wants to convert 2 .pub files for me? I have received 2 pub files I have to work with (90 kb and 180 kb), no idea what version, i tried adobe Converter 1.0 for Microsoft Publisher 95 and 97 that didn't work. Googled for ways to convert, but that doesn't seem possible. Looked to download the trial version as a last option but there only seems be a cd trialversion. But I have to get it quickly, this weekend so i can do something with it. If someone can convert it to pdf or text, (I believe it's only dutch text) i would be very happy. Anybody I can sent t...