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

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
cybersiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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
cybersiAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
cybersiAuthor Commented:
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
cybersiAuthor Commented:
looking in more detail it needs to be a xls file
0
Rob HensonFinance AnalystCommented:
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
cybersiAuthor Commented:
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
cybersiAuthor Commented:
thanks guys I think I have fixed this stage thanks to both of you
0
cybersiAuthor Commented:
Hi Phillip

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

Thanks

Simon
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Insert after the first line:

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

Open in new window

0
cybersiAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
That's fine. I actually immediately after the line starting "Sub", but your version will work as well.
0
cybersiAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.