Solved

How to merge/combine two cells in excel that start with zeros

Posted on 2014-09-30
14
251 Views
Last Modified: 2014-09-30
Hi

I am trying to merge to 2 cells using the formula =a2&" "&b2 but it keeps stripping out the zeros.  Please can I have help to fix this please

Regards

Simon
combine.xlsx
0
Comment
Question by:cybersi
  • 8
  • 4
  • 2
14 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 450 total points
ID: 40352053
Either

=TEXT(A2,"000000") & TEXT(B2,"0000")

Open in new window


or

=TEXT(A2,"000000") & " " & TEXT(B2,"0000")

Open in new window


depending on whether you want the space (which is in your text) or not (which is not in your spreadsheet "what it should be" column)
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40352098
The reason why you are getting the result you are seeing is because when using the & to concatenate items it purely takes the contents of the cell and does not use any formatting from the source cells.

Your source cells have custom formatting to show 6 or 4 digits, this is ignored when linking to the cells.

As Philip has correctly shown, the result can be achieved as required by using the TEXT function. The number of zeros included in the double quotes will determine how many digits are used.

Even more confusing is when you are using dates. Imagine the scenario, your source cells contains a date nicely formatted eg 30 Sept 14 but when using this within a concatenation it would become 41912 because that is the serial number for that date.

Thanks
Rob H
0
 

Author Comment

by:cybersi
ID: 40352117
When my system generates the xls file is there a way to run a macro to automatically add the 4th column add format the text as a =TEXT(A2,"000000") & "" & TEXT(B2,"0000")   ?


I have no experience with macros

thank

simon
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 450 total points
ID: 40352122
Sub CreateSheets()
introw = 2
Do Until Cells(introw, 1) = "" And Cells(introw, 2) = ""
    Cells(introw, 4).Formula = "=TEXT(A" & introw & ",""000000"") & "" "" & TEXT(B" & introw & ",""0000"")"
    introw = introw + 1
Loop
End Sub

Open in new window

0
 

Author Comment

by:cybersi
ID: 40352136
Hi Phil

Do I save the above as a script like a vbs file?  My system as shown in the attached has the ability to run macros.

As I have said zero macro knowledge.

Thanks

Simon

PS your answer worked a treat. I did have to remove the space between the ""
macro.png
0
 

Author Comment

by:cybersi
ID: 40352147
looking in more detail it needs to be a xls file
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 50 total points
ID: 40352153
Do you really need a macro to add one formula or are you expecting it to do other things as well?

If there are numerous continuous rows against which you want the formula added, enter it in one and then use the mouse to hover over the bottom right corner of the cell until the cursor changes to a bold + symbol. Double click here and the formula will be copied down the extent of the data.

Alternatively, if you do want a macro to insert the formula it is relatively easy to do.

Assuming the Developer tab is not visible on your toolbar, get it visible by going to:

File tab > Options > Customize Ribbon - In right hand pane tick the box against Developer. Click OK.

On this new tab click Record Macro, in the window that appears give the Macro a name; spaces are not allowed but you can use underscore to separate words if required, select Personal Workbook from the drop-down and click OK. A stop Recording button will appear in the status bar at the bottom. Enter the formula as required in the first cell and then click the stop button.

You have just created your first Macro!!! :-)

You can then double click on the bottom border of the cell as mentioned before to fill down the extent of the data.

For future use, when you have the new file created and need the formula inserted; select the first cell requiring the formula and then select the Developer ribbon tab. Click on the button in the middle of the first Group with title Macros. This will show a list of macros available in your current session. Select your insert formula macro and click Run. This will insert the formula into the first cell, check it is as expected and then double click the corner as before to fill down.

Hope that is clear.

Thanks
Rob H
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:cybersi
ID: 40352169
Hi Rob

This is just the first stage of automation that I am trying to do.  Basically the goal is to download bank statements from our customer bank accounts and use a report downloaded from out system and then create a new spreadsheet with all the right formatted data and then import that back into our system.  The reason is when you down load a statement from any back it merges the reference field and the customers name into one field.  I have a vb script which strips all the data and leaves only the urn number (which should be a tenant code) and the amounts they have paid in.

Hope that makes sense :)

Simon
0
 

Author Comment

by:cybersi
ID: 40352296
thanks guys I think I have fixed this stage thanks to both of you
0
 

Author Comment

by:cybersi
ID: 40352434
Hi Phillip

The macro works great but is there a way to give the column a title called "URN" automatically?

Thanks

Simon
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40352443
Insert after the first line:

Cells(1, 4).Formula = "URN"

Open in new window

0
 

Author Comment

by:cybersi
ID: 40352452
like this?

Sub CreateSheets()
introw = 2
Cells(1, 4).Formula = "URN"
Do Until Cells(introw, 1) = "" And Cells(introw, 2) = ""
    Cells(introw, 4).Formula = "=TEXT(A" & introw & ",""000000"") & "" "" & TEXT(B" & introw & ",""0000"")"
    introw = introw + 1
Loop
End Sub
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40352459
That's fine. I actually immediately after the line starting "Sub", but your version will work as well.
0
 

Author Comment

by:cybersi
ID: 40352480
Hi Phillip

It works like a dream thank you so much. I could never of done this without your help.

Don't fancy having a look at another open question for me do you?  (please)

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28528369.html

Thanks again

Simon
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

760 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