Free Microsoft Excel 2013 Quick Reference

Merge two columns with a space Results

Hi guys

My first post here!

I have two columns in my Excel worksheet - A & B. Column A contains numbers (not consecutive) and column B contains text.

What I want to do is merge column A with column B and put a space between the merged number and text.

E.g.

Column A Column B
234 XYZABC

I want a new column to contain 234 XYZABC. There are about 800 rows.

I hope I've explained this adequately!

Cheers

John

Hello all,

Just need a little help writing a macro that will merge two columns together. The columns marked “Retained Notes” and “Frozen Notes” need to be merged into one column simply marked “Notes.” The text (if any) in both columns needs to be merged into one cell in a single column with a space between the text. “Retained Notes” and “Frozen Notes” are the headers in row one. Retained Notes and Frozen Notes are always next to each other, but sometimes not in the same column letter (sometimes in col P sometimes in col O somtimes in col N, etc.). This is why I want to use the column headers in row 1 to identify the columns to merge.

See attached example.

Hello all,

Just need a little help writing a macro that will merge two columns together. The coliums marked “Retained Notes” and “Frozen Notes” need to be merged into one column simply marked “Notes.” The text (if any) in both columns needs to be merged into one cell in a single column with a space between the text. “Retained Notes” and “Frozen Notes” are the headers in row one.

Retained Notes Frozen Notes
Notes1 Notes2

Becomes:

Notes
Notes1 Notes2

Please tell me how to merge two adjacent columns with a space between them. Both columns contain text and numerals.
For example:

212 by Carolina Herrera EDT SPRAY 1.7 OZ

Thank You,
rooniwife

www.spotajavacoffee.com

Excel 2003

Good Morning or Afternoon as the case may be!

I'm working with a large dataset that has two columns that will need to be merged together.

Column A is first name
Columb B is last name.

I need a column that take A and B and merges them together seperating them by a space, resulting in text that look like "First Last"

Thanks for your time!
-Yak

Hi there,

A few questions:
1) how to convert a column [or row] of data into upper case?

2) how to merge two columns of names into 1 column where a space separates the two names

3) how to convert the names in a column to mixed case, that is, the first letter of a name is in upper case, the remainder lower-case? Hmm how to work with names like McCain or O'Hanlon, or Smith-Jones?

Would a book like 'excel for dunmmies' help here, or please recommend your gav book.

Thanks for the assist. I hope I can help others soon too!

In column A I have names, in column B I have another set of names.
How do I get both sets of names in 1 cell, as a continuous string, with a
space between each string?

Howdy!

Here is my dilemma: I have four columns of data A,B,C and D.

Column A: series of numbers starting from 0 and ending with 4388, though not every number in between is used (these are actually ID numbers for ALL employees, past and present).

Column B: series of numbers starting with 456 and ending with 4388 (same as above, they represent the ID numbers for employees, however Column B represents only ACTIVE employees, not all employees).

Column C: another column of numbers, each one is specific to the number in Column A, however a number may be used more than once (the numbers in this column represent the number of active clients still with our company. Even if the employee is no longer with us, their clients may still have an active product with us).

Column D: this column contains the name of the employee, however this is associated with the number in Column B (I only have the names of all active employees).

So my my data looks something like this (obviously ignore the periods, they're just for spacing purposes):

......A......B......C......D
1]...0.....456....36....Ben L.
2]...1.....875....22....Marvin S.
3]...2.....896....6......Gayle F.
4]...4.....951....13....Ed W.
5]...5....1120....3.....Bob L.
6]...6....1152...12....Terry S.

As you can see, Column A and B do NOT match numbers. So here is what I'd like: have Column A and B align to the same numbers, which means Column C or D would have to change as well (I don't care which column is static or such, I just need them all to match). So, what I would like it to look like is this (once again, please ignore the periods):

.......A........B......C.........D
1]...456....456...2871....Ben L.
2]...875....875...1957....Marvin S.
3]...896....896...1916....Gayle F.
4]...951....951...1741....Ed W.
5]..1120...1120....1......Bob L.
6]..1152...1152..1519...Terry S.

So I have Column A and B matching together, Column C displaying the correct number associated with Column's A and B, and Column D displaying the correct name associated with Column's A and B.

Now, I've already examined this thread:

Sorting Two Threads With Similar Data

and haven't had any luck. Perhaps I'm not understanding how to properly utilize the formula, or maybe it's not a viable solution.

I've also examined this thread:

Aligning Columns With Same Data

and likewise, no results. Maybe I'm not executing the VBA script correctly, or misunderstood how the script works.

So, with that out of the way, I now turn to you, the great excel gurus, to try and provide me with some tips / tricks / solutions! I've attached a sample of the notebook I'm working with (have no fear, all last names were removed from the employees) for you to try and toy around with. Any and all help will be appreciated! Thanks in advance and I look forward to seeing what happens!

~Michael Auto Merged Post Until 24 Hrs Passes;

oops! Forgot the attachment!

I have a spreadsheet that hass a bunch of drop down arrows and I am having some problems with them, so here goes.

I have cells A1 and A2 merged (in this merged cell is "option #1")
I then have cells A3 and A4 merged (in this merged cell is "option #2")

I then have cells B2 and B3 merged with a drop down arrow so that you can pick between option 1 and 2.

When I select the range for the drop down arrow, I click on my 2 merged cells in column A, but instead of just giving me the selected cells, it gives me the range of A1:A4. Therefor my drop down arrow looks lilke the following:

Option #1

Option #2

There is a space between the two options. Doesnt seem like much of a big deal, but as my spreadsheet goes on, towards the end there is so much space between the two options that you actually have to scroll to see both options.

Hope I gave yo enough info without confusing you too much.

Thanks in advance for your help,
Derek

I am looking for a way to merge the data in two fields and separate it with a comma. I'm not sure how to do this. Example: field 1 - Last Name, field 2 - First Name, field 3 - "Last Name" comma space "First Name". I need to do this to merge this spread sheet with another spread sheet that has one field for the name instead of two. For now I will continue typing the first names into the proposed new column.

Hi there
Can someone help with the following query re merging similar cells?

I have a spreadsheet with two columns, a simplified example is below:

Column A Column B
1 Apples
1 Bananas
2 Cherries
3 Tomatoes
4 Oranges
4 Pineapple
4 Plums

Now I want to merge all similar items into one row, ie I want it to look like this:

Column A Column B
1 Apples; Bananas
2 Cherries
3 Tomatoes
4 Oranges; Pinapple; Plums

Any thoughts? It's not imperative that there is a semicolon between words in column B, just a space would be okay.

Thanks

I have a spreadsheet with all kinds of data and I want to use mail merge to write letters in Word. I am having a problem with one of the columns not merging correctly. In that column I have client numbers. Most clients only have one number and in that case the merge works fine. Some clients, though, have two or more numbers and so I have the numbers separated by a space. When I try to do the mail merge, the ones with multiple numbers display a "0". Here is an example of what I am talking about:

In an excel column:
Client Number
1234
2345
3456
8765 7654
4321 5432
2468 3579 7531

When I merge it this is what I get:
1234
2345
3456
0
0
0

All I want is for the numbers to be displayed at the bottom of the letter the way I have them typed in excel.

I have tried changing the format of the column to text and that doesn't work. I've tried separating the numbers by a "-", "/", "*" and that doesn't work either.

One last funny thing - If I delete all the rows that have single numbers in them and leave only the ones with multiple numbers, it works fine. It's almost like the mail merge feature doesn't like single numbers and multiple numbers in the same sheet.

Any help would be greatly appreciated!

i have two columns. column a and column b. i need the adjacent cells in each row for those columns to combine the contents into one cel. for example. column a row 1, should merge with contents from column b 1.

i don't need any special spacing, i just need them to merce, how can i do that.

thanks
jacob

I have two worksheets. The first worksheet contains a complete list.

Column A in the first worksheet contains names and SS# (that appears to have originally been a merge of last name first name MI and SS#, which later was probably copied and pasted special as a value) so now all it shows is Last name comma First name MI space xxx-xx-xxxx. For example A216 contains Doe, John A 123-45-6789

In the column F (next row down) it has a date. So on F217 there is 07/11/2007

The second worksheet contains a partial list.
In column A some of the SS# contained in the first worksheet, but just as a 9 digit number. Column B contains First name, Column C contains last name. I have added column E for the date, and wish to point it to the first worksheet to pull the date there.

So in the second worksheet, B2 contains 123456789 and I want to find the corresponding 123456789 in the first worksheet in A216 and pull F217 into E2.

Can this be done? Or maybe as a couple steps - somehow break apart Column A back into it's parts so that all I have is a SS# to match to?

Hi Group,

I have a question on using VBA for merging information in cells and
changing the format. We do have a printout from a tabulation program
(WinCross) which looks the following:

Blue Green Yellow

(A) (B) (C)

46.5% 35.4% 43.0%

Bc AC aB

That means, Blue (46.5%) is significantly different at the 95% level
from Green and at the 90% level from Yellow

This is spit out for about 100 tables. We now want to merge some of
that info and also have the format changed, i.e.

Blue Green Yellow
47%(B) 35%(A,C) 43%(B)

In words: we need the percentage without the decimals and then the
capital letters in parentheses, separated with a comma. To make things
worse, it should also have the (A,C) et al. in superscript.

Is there a way how I can do that in VBA? I have a rough idea how to
get the two cells together, but I stumble over three things:

1) When I merge the cells, I get endless decimals, i.e.
..4653988798798(B) for the first column.

2) I would need nothing if there is nothing in terms of significance,
but an open bracket, closed bracket if there is at least one
significance indicator and commas if there is more than one letter. So
if it is the last significance level, there would be a closed bracket,
if there is at least one more coming, there would be a comma and a
space)

3) How do I get the superscript formatting for everything from the
fourth character on?

Thanks a bunch, I will give Santa Claus a letter of recommendation for
all those who make suggestions.

Katsche

I would like to take any data in column B and merge it into column A with the two following requirements:

1. numbers are merged in sequential order

2. in column A, a space is placed above any number merged from B

I have attached my typical worksheet. In column D I have input how I would like column A to look when finished. Thank you to anyone offering any insight on how I can get this done.

I have a few basic questions Hope you can help me.
I have Danish Office 2007.

1) Let's say I have 3 columns (horisontal) and many rows (vertical. Each row goes together, as the first column can be text that describes a transaction. The second column will be the amount of money and the third can be something else.
I know how to sort these by whichever column I want to. But the problem is every cell needs to be the same size. And I have merged 3 cells in the first column and only merged 2 cells in the last two colums.
So Excel tells me I cannot merge unless each cell is the same size.

Is there a solution here? I need the 3 cells in the first column, so I have enough room to describe the transaction. And to avoid wasting space, I need to only have the other two columns be the size of two merged cells each.

Example, though with the text in another column:
Picture of it

2) The other problem is that I would like a cell to display the number in red if below zero, green if above zero. I cannot do this. I know where to put in the format codes, but I don't know what to write.
At the same time, I need the cell to show the currency in Danish Kroner. So I need a format code that does this. Someone told me this:
[Blue]dk #.##0;[Red]dk #.##0;[Green]*dk #.##0

But that doesn't work, nor if the color is in danish. What do I do?

Hi guys,

I'm sure this sort of thing has been discussed so if appropriate please point me to a relevant tip or topic but I am going to put my case clearly as I really need to get some data formatting for a business card data merge with InDesign.

I have to ensure that all the data is formatted uniformly across the business and this includes separating all the numbers in the business cards with a single space and finally including the p/f/m identifier and outputting as csv...

The spreadsheet looks like this
The columns are:
first name, surname, title, division, address, phone area code, phone1, phone 2, fax area code, fax 1, fax 2, mobile 1, mobile 2, mobile 3, email, url suffix, switch number

I have several problems the first is the tendency for any leading zeros to be dropped unless the data is saved only once as csv/txt - ie as soon as excel works with the data again the leading 0 gets dropped because the original formatting of the xlsx file is lost and the columns revert to standard number formatting. Therefore I have to be very careful that I export the data as a text file only once and not 'resave' it upon closing.

The next thing is to "build" the full fields that InDesign will need to populate the data. I need to output the data in the following format

first name, surname, title, division, address, phone, fax, mobile, email, url, switch

Now the problems occur when there is no fax and extra spaces get thrown into the layout in InDesign since for example we have

p: 02 9805 0633 f: m: 0405 195 022

or worse the number sets have been mangled and we get:

p: 2 9805 633 f: m: 405 195 22

What I have had to do is enter the numbers in the spreadsheet pre formatted:

column for phone therefore is text:
"p: 02 9805 0633"
fax is completely blank and mobile is
"m: 0405 195 022"

InDesign handles this ok if I run a grep/replace for two m_spaces into one.

But what I want to do is run a function or script of some kind from the raw spreadsheet data (because I will ultimately get this from a php engine)

and from

2, 9805, 633

create

p: 02 9805 0633

ie test several things and ensure that the phone is correctly formed:

start with "p:" check that the area code is "02" and of only "2" make "02", add a space, ensure that the second set of numbers is 4 digits, and the second is 4 to test if it has a leading 0 dropped and so on...

Gee, guys this sounds harder to explain than it would be for someone experienced with this stuff and once I know how to do it the logic will fall into place but so far my Excel 2007 skills have left me clueless..

Any assistance, pointers and mentoring would be most appreciated!

Tia

Will

I did get a partial response, thanks Barb, but now I need to dig into
VBA and I stumble.

I guess the formatting needs to be done in VBA and the easiest way is
by copying each cell and pasting the values (otherwise, I am not sure
if I can do partial formatting of a cell, i.e. superscripting parts of
it).

So the code would read:

Range("c5:g5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With ActiveCell.Characters(Start:=4, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = True
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 23
End With

The Problem is the Range. For Copying:Pasting, I can do such a range
but not for the formatting, i.e. I would need an individual range for
each cell.

How do I do that in a loop command, i.e. something like

dim intCol as integer
for intCol = 1 to 10
if intCol <= 10 then
----- all that formatting ----
end if
next intCol

This does not work for whatever reason, is anybody able to help out?

Thanks

Katsche

Hi Group,

I have a question on using VBA for merging information in cells and
changing the format. We do have a printout from a tabulation program
(WinCross) which looks the following:

Blue Green Yellow

(A) (B) (C)

46.5% 35.4% 43.0%

Bc AC aB

That means, Blue (46.5%) is significantly different at the 95% level
from Green and at the 90% level from Yellow

This is spit out for about 100 tables. We now want to merge some of
that info and also have the format changed, i.e.

Blue Green Yellow
47%(B) 35%(A,C) 43%(B)

In words: we need the percentage without the decimals and then the
capital letters in parentheses, separated with a comma. To make things
worse, it should also have the (A,C) et al. in superscript.

Is there a way how I can do that in VBA? I have a rough idea how to
get the two cells together, but I stumble over three things:

1) When I merge the cells, I get endless decimals, i.e.
..4653988798798(B) for the first column.

2) I would need nothing if there is nothing in terms of significance,
but an open bracket, closed bracket if there is at least one
significance indicator and commas if there is more than one letter. So
if it is the last significance level, there would be a closed bracket,
if there is at least one more coming, there would be a comma and a
space)

3) How do I get the superscript formatting for everything from the
fourth character on?

Thanks a bunch, I will give Santa Claus a letter of recommendation for
all those who make suggestions.

Katsche

Reply

Barb Reinhardt
Dec 28, 1:10 pm show options
Newsgroups: microsoft.public.excel.misc
From: "Barb Reinhardt" <r...@tonewsgroup.com> - Find messages by this
author
Date: Wed, 28 Dec 2005 13:10:05 -0500
Local: Wed, Dec 28 2005 1:10 pm
Subject: Re: Merging Info in Two Cells
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

I can help with part of it

> 1) When I merge the cells, I get endless decimals, i.e.
> .4653988798798(B) for the first column.

Assuming 0.465 ... is in A2 and (B) is in B2
Try
=TEXT(A2,"0%")&B2

<Katsche.Schwarzenb...@gmx.net> wrote in message

Hello im a vb noob.

any one can help me solve my problems.

i have a code here from Ron de Bruin.
that merge text file.

my problem is how can i filter my data, range:all in column A that starts with value:A, AB or ABC and paste it the entire row to the other worksheet using macro.

thanks.

Declare Function
OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub


Sub Merge_CSV_Files()
Dim BatFileName As String
Dim TXTFileName As String
Dim XLSFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String
Dim Wb As Workbook
Dim oApp As Object
Dim oFolder
Dim foldername

'Create two temporary file names
BatFileName = Environ("Temp") & _
"CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
TXTFileName = Environ("Temp") & _
"AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

'Folder where you want to save the Excel file
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "" Then
DefPath = DefPath & ""
End If

'Set the extension and file format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
'If you want to save as xls(97-2003 format) in 2007 use
'FileExtStr = ".xls": FileFormatNum = 56
End If

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterCSV " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

'Browse to the folder with CSV files
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) <> "" Then
foldername = foldername & ""
End If

'Create the bat file
Open BatFileName For Output As #1
Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
& Chr(34) & " " & TXTFileName
Close #1

'Run the Bat file to collect all data from the CSV files into a TXT file
ShellAndWait BatFileName, 0
If Dir(TXTFileName) = "" Then
MsgBox "There are no csv files in this folder"
Kill BatFileName
Exit Sub
End If

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False

'Save text file as a Excel file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
Application.DisplayAlerts = True

Wb.Close savechanges:=False
MsgBox "You find the Excel file here: " & vbNewLine & XLSFileName

'Delete the bat and text file you temporary used
Kill BatFileName
Kill TXTFileName

Application.ScreenUpdating = True
End If
End Sub