CONCATENATE problem with blank cells

I need to merge columns A and B, but some cells are blank, so I can't
copy the formula down the 3rd column...it stops at the 1st blank

a1 Frank Smith
a2 blank 
a3 blank
a4 Bill Jones

b2 blank
b2 sally
b3 billie
b4 blank

HELP?


-- 
roger_home
------------------------------------------------------------------------
roger_home's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16434
View this thread: http://www.excelforum.com/showthread.php?threadid=396618

0
8/17/2005 7:34:03 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
434 Views

Similar Articles

[PageSpeed] 34

Hi Roger,

You could Concatenate the Cells in Columns "A" & "B" Using the
Following Formula Entered into Cell "C1" and Copying Down :-
=CONCATENATE(A1,B1)
OR
=A1&B1

Hope this Helps.
All the Best.
Paul



roger_home wrote:
> I need to merge columns A and B, but some cells are blank, so I can't
> copy the formula down the 3rd column...it stops at the 1st blank
>
> a1 Frank Smith
> a2 blank
> a3 blank
> a4 Bill Jones
>
> b2 blank
> b2 sally
> b3 billie
> b4 blank
>
> HELP?
>
>
> --
> roger_home
> ------------------------------------------------------------------------
> roger_home's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16434
> View this thread: http://www.excelforum.com/showthread.php?threadid=396618

0
8/17/2005 8:18:45 PM
Reply:

Similar Artilces:

Line Deleting Problems
When I draw a line and come back to it later to delete it, Publisher 2002 will not recognize the line when the cursor is on top of it--anywhere. Therefore, I am unable to edit the line. HELP! Is this a software problem? Linda After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Linda <Linda2NPBC@aol.com>... > When I draw a line and come back to it later to delete > it, Publisher 2002 will not recognize the line when the > cursor is on top of it--anywhere. Therefore, I am unable > to edit the line. Do you have text boxes or other objects coveri...

Any problems with 10.3.x?
I am planning to upgrade from 10.2.8. I mostly use Word and Excel and both are working fine now. Are there any known issues with 10.3? -- Robert B. Peirce, Venetia, PA 724-941-6883 bob AT peirce-family.com [Mac] rbp AT cooksonpeirce.com [Office] In article <bob-21F232.10491602012004@msnews.microsoft.com>, Robert Peirce <bob@peirce-family.com.invalid> wrote: > I am planning to upgrade from 10.2.8. I mostly use Word and Excel and > both are working fine now. Are there any known issues with 10.3? I've been using Panther for several months now with no problems. ...

Business contact manager
If I set a reminder in business contact manager with a reminder date, it doesn't show up on the listing of Business contacts (which I have set up as a table). I set up the 'Reminder Time' field from 'All Contact fields' in the field chooser. If I enter a date into the field on the table it momentarily gets reformatted to show day of week for example, but within half a second the entry blanks out. Bob ...

Problem with Import/Export
Hi, I have a problem with Outlook 2003. When I try to import or export to/from my PST file, I only get messages, contacts, appointments or tasks up to November 2003 - which is the date when I reformatted my computer and installed O2k3 (I imported my backup PST into Outlook 2003). I tried to repair the PST with scanpst.exe - but the problem persists. Any ideas on how to "remind" the file that it has newer items inside - will be greatly appreciated! Many thanks. This is rather a vague question. Importing and exporting are two different operations. I'm having a hard time unde...

Balance problems
To start I manually enter all transactions. I'm having troble with balances on the screen everthing is good then when I go to print I get a very different running balance. It started a couple month ago. I even started from scratch and after balancing last months checking account it is doing the same thing. I have been using money for many years and this is the first time I've seriously thought of changing programs In microsoft.public.money, tvan wrote: >To start I manually enter all transactions. I'm having troble with balances >on the screen everthing is good then wh...

retrun blank if error
I am trying to create a dual entry system, a1=b1, and b1=a1; if I enter a value in either a1 or b1 it is copied to the other. Can I program both to return a blank with the circular ref error. This returns 0 not blank:for a1 =if(iserror(b1),"",b1) and for b1 =if(iserror(a1),"",a1) I am confused.........easily done<g> How do you enter a value in a cell that contains a formula? Gord Dibben MS Excel MVP On Thu, 22 Nov 2007 12:11:18 -0800 (PST), chegel <chegel@gmail.com> wrote: >I am trying to create a dual entry system, a1=b1, and b1=a1; if I >ent...

count number of cells
What is the formula to count the number of cells that start with a particular character? What I really want to do is count how the number of cells in a column that start with a through e. I have tried several things but can't quite come up with the magic formula. thanks for your help. =COUNTIF(A1:A20,"A*")+COUNTIF(A1:A20,"B*")+COUNTIF(A1:A20,"C*")+COUNTIF(A1:A20,"D*")+COUNTIF(A1:A20,"E*") -- David Biddulph "tagout" <tagout@discussions.microsoft.com> wrote in message news:2B624B5C-E73A-4481-A3E7-C9207F72DAB...

Displaying cell contents
I have a spreadsheet that has a formula in it in cells C1:J50. The cells are displaying 0 because there is nothing to calculate. How can I keep my formula for calculating when actual numbers are entered but the display will show an empty cell, not 0. Thanks, Blue Angel In your formulas you do some variation of this: =if(a1="","",yourformula) Regards, Fred "BlueAngel" <BlueAngel@discussions.microsoft.com> wrote in message news:87342869-89DD-4829-9EA0-0B077964B5DF@microsoft.com... >I have a spreadsheet that has a formula in it in ...

Problem: Playing CD Music in PowerPoint presentation
I want to play 3 tracks of music in a PowerPoint presentation. I put the CD in the optical drive. In the PP, at the first slide, selected Insert, Movies & Sounds, Play CD Audio Track. It starts at track 1, ends at track 1, and plays automatically. In the Custom Ainmation box, I chose the Special Effects and asked the track to play from the beginning, and stop after 4 slides. Everything is wonderful because this works and the music plays for the first 4 slides. On Slide 5 I do exactly the same thing, and select track 2 on the CD, and ask it to play for 3 slides. Here&...

Copy Partial Cell Contents in Excel ? #2
I will definitely work through those responses and give them a try! Los this thread for a little while, but FOUND it again! Thank you very muc for your help Sirs! Best, Ma -- MacDubhga ----------------------------------------------------------------------- MacDubhgal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1424 View this thread: http://www.excelforum.com/showthread.php?threadid=25899 ...

Replacing Contents of 1 Cell to Another.
My Spreadsheet looks like below. A B C 207 MDIM3030 3 TRUE 208 MDIM3030 FALSE 209 MDIM3034 1 TRUE 210 MDIM3034 FALSE 211 MDIM3038 6 FALSE 211 MDIM3039 4 FALSE I have sorted by Column "A" and done an EXACT on them. Now I need to know how to, for example on cell, B:207 (3) and move i to the blank cell below and so on through the Column. There ar hundreds of lines on the column, so I am looking for a...

CryptAcquireContext problem in Win98
Hi, I created CryptAcquireContext in win2000 and it is working successfully and when i installed in Win98 (Fresh system) it fails, i gote the error "Provider's public key is invalid " any one tell me how can i able to avoide this error. I invoked the functions as follows CryptAcquireContext (&hProv, NULL, NULL, PROV_RSA_FULL, 0) Thanks in advance Rgds Dinil, ...

Color cells that match on two sheet
Hi Everyone Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "...

address sorting problem?
I have a mailing list with the standard information; company, address, city etc. lots of cities and streets What I want to do is sort the names (within city) by address, but by street name before number, I want to wind up with: 1 adam st 2 adam st 5 adam st 4 brown st Not brown in the middle of adam Is there a way to "strip" a number portion off of the address column and create a new "#" column? then I could sort by "street"/"#". Thanks, Frank Hi yes, you can 'strip' the number portion off using two helper columns, say your addresses are in...

splitting contents of a cell #2
Hi all, I have a list of addresses split over five or so columns. One of the columns has the format "# street name". Is it possible to write a formula that lifts the street name out of that cell but not the number? so from a cell with "25 Hight Street" I would want just "High Street". hope that makes sense, thanks, luc if cell A1 has 25 HIGH STREET, the following formula in B1: =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) will extract HIGH STREET. -- icestationzbra ------------------------------------------------------------------------ icestationzb...

DDE problem between Word and Excel
For years I have been using Word programs (Office 2000 series) to access Excel name and address data files to produce letters, mailing labels, etc. Now, when executing a Word merge program, I am all of a sudden receiving the message, "Waiting for Microsoft Excel to Accept DDE Commands." The computer then hangs up, as if in a loop. This is true for all my Word merge programs. Nor can I create a new merge document in Word, because the program hangs up when attempting to access the Excel name and address file. What has happened? Thanks in advance. Gordon Biggar Houston, Texas P....

MS CRM-Exchange E-mail Routep setup problem
Hi, I have a question for you. I have MS Exchange 2007 & CRM 3.0. Exchange E-mail router installation fails. On System Requirements step i have error: 1. Microsoft Windows Operating System - OK 2. Microsoft CRM-Exchange E-mail Router Setup User Input - OK 3. The Microsoft Exchange administration tools are not installed on this server. What is "Microsoft Exchange administration tools" or where i can download this tools? You need to install router on exchange 2003 and this update is for exchange 2007 mail boxes but you still need exchange 2003. /aamir http://www.microsoft...

Problem with distribution lists
Hi I've recently had problem where messages sent to a distribution list do not go through... I can actually see them sittin in the queue viewer and the status on them is "retry"... I've had some success with other distribution lists where I actually recreated the list and sent a message to it and it went fine, but others don't get relayed through... Anyone ever had this problem before... I'm running a mixture of Exchange2000 and Exchange2003 with a couple of machines in each admin group.. Tks "Oskido" <eskimo@myiafrica.com> wrote: >I've ...

Merge Text From Two Cells
I would like to merge texts from two different cells into the third cell. Example:- Cell A1 - Tom Cell A2 - Jones Cell A3 - Tom Jones I am using this formula in cell A3 :- =A1&""&B1 But I get TomJones (combined into one word) instead of Tom Jones (two words). Can someone help me to modify the code/formula so that I get Tom Jones (two words) Warmest regards, Salza Hi Salza! Use: =A1&" "&B1 Note the space between the double quotation marks -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Functio...

Problem while using Word as Outlook-mail-editor
I am using Outlook in windows-xp platfarm. Also I have selected WORD as the mail editor. It was working well. Now, if I open new-mail, word is not responding and it just hangs. I have to press ctrl-alt-del to come out from outlook. If I disable 'word' as editor, I can able to prepare new mails,can make reply & forward etc. kindly suggest a solution regards Selvaraj What version of Office are we talking about here? Does Word work without issues on its own? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Cr...

named cell referenced in header
I would like to have a cell (company_name) on the first worksheet be referenced within all the headers in the workbook. How do I do this? Using code, you would paste this in a workbook module: Code: -------------------- Sub setHeader() Dim mySht As Variant Dim i As Integer For Each mySht In Worksheets mySht.PageSetup.LeftHeader = _ Format(Worksheets("SetUp").Range("A1").Value) Next End Sub -------------------- When run what this will do is paste whatever value is on a sheet named "Setup" in cell A1 to the left header of all the ...

Visio Viewer shows blank pages in Internet Explorer 6
Hi All, My PC installed Visio Viewer 2003, but everytime I open Visio file, IE opened, and I accepted the ActiveX, but Blank Page appear and cannot see the Visio content ! I tried to reinstall Visio viewer 2003 ( even 2007 ), but cannot fix !, I tried another computer opened this visio file and it's work ! How can I fix this problem ? Do I need to re- install IE 6 or upgrade to IE 7 ? Thanks a lot, ------=_NextPart_0001_64EA523E Content-Type: text/plain Content-Transfer-Encoding: 7bit It's possible that you may be missing one or more IE6 Security updates, as we have seen this occ...

MultiThread and socket problem....(I'm making a simple EchoServer.)
Hi, group. I've made a simple EchoServer using MFC CSocket and CWinThread(for multithreading.) I've referred below article on MSDN KB. MultiSoc: Illustrates Using Sockets in Multiple Threads http://support.microsoft.com/default.aspx?scid=kb;EN-US;175668 It works fine with a small problem. :( Since I've passed the handle of socket, each thread get notified when there is a incoming data on socket. I want to reflect(or broadcast) this incoming message to all connections. That is, since my server is a echoserver, I need to notify all clients about newly incoming message. So I ...

Create Checkbox and link to relative Cell
Hi, I have a script that I am using that creates a checkbox in a range but am having problems trying to create this for the relative cell and linking this to a relative row cell, i.e. if the current cell is R16 create a checkbox and link the value to S16. Sub CellCheckbox() Range("R15").Offset(1, 0).Select For i = 1 To 50 'add the checkbox ActiveSheet.CheckBoxes.Add(646.5, 203.25, 24, 17.25).Select ActiveSheet.Shapes("Check Box 1212").Select Selection.Characters.Text = "" With S...

Colouring any cell in worksheet with negative value
Hi all, I have written the below code for making the cells from column to A to BB fill with red colour, if they have a negative value in them. I can't seem to make it work. Any help will be appreciated! Thanks very much! Shivam ****************************** Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const ColumnsToCheck As String = "A:BB" If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then If Target.Value < 0 Then Target.Value.Select Selection.Interior.ColorIndex = 3 End If End If End Sub...