Merge Two Rows?

davidbook

New Member
Joined
Nov 3, 2002
Messages
4
I need to merge two rows into one? First Row has Name, Address of client (client list), second row has phone number. Phone number is under address cell, in address column, ugh. I have created a new empty cell next to address to eventually hold the phone number How can I get phone number into the new cell?
This message was edited by davidbook on 2002-11-04 18:24
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

cnank

New Member
Joined
Jul 16, 2002
Messages
23
say cell A1 has name of client = smith
cell b1 has phone number = 999-9999
in cell c1 put in :
=a1&" "&b1
this will yield smith 999-9999

the " " puts in the 2 spaces between name and number
 

davidbook

New Member
Joined
Nov 3, 2002
Messages
4
On 2002-11-04 18:11, cnank wrote:
say cell A1 has name of client = smith
cell b1 has phone number = 999-9999
in cell c1 put in :
=a1&" "&b1
this will yield smith 999-9999

the " " puts in the 2 spaces between name and number

There are 11,000 records like this. I have created an empty cell next to the cell containing the address. The phone number is "under the address" currently in the address column. How can I move it "over one cell" and "Up one cell". Hope this makes sense, I am a rookie
 

cnank

New Member
Joined
Jul 16, 2002
Messages
23
IF the format for your address and phone number are all the same, in other words, the phone number is ALWAYS in the format (999) 888-1000, then you can do it. your want to extract the text string. Since the phone number is alwasy at the end I will assume you could do something like this:

1701 Grant Ave.
johnstown, MA 19999
(999) 888-1000

Since I want the last 14 charachters (including the brackets dashes and spaces) I would make a cell that has =right(a1,14)
that would give me the rightmost 14 charachters from cell a1. Use the function help to look at the textstring functions for right and left.

If the phone number is not in a constant format it is more difficult. I will assume it is formatted unless you say differently.
 

cnank

New Member
Joined
Jul 16, 2002
Messages
23

ADVERTISEMENT

by the way I assume you have this:

A1 = smith, john : 1201 allen street
b1 = (999) 888-1000

if you combine my two answers you can do it, but again, ONLY if there is some format like constant number of charachters for the name.

You can also use the FIND function to find commas or colons or whatever format charachter you are using to seperate the name and address, then embed the find command into t left or right text function. You can also use the concatenate funtion to join text strings. Again, look at the function help for text functions.
 

davidbook

New Member
Joined
Nov 3, 2002
Messages
4
On 2002-11-04 18:28, cnank wrote:
IF the format for your address and phone number are all the same, in other words, the phone number is ALWAYS in the format (999) 888-1000, then you can do it. your want to extract the text string. Since the phone number is alwasy at the end I will assume you could do something like this:

1701 Grant Ave.
johnstown, MA 19999
(999) 888-1000

Since I want the last 14 charachters (including the brackets dashes and spaces) I would make a cell that has =right(a1,14)
that would give me the rightmost 14 charachters from cell a1. Use the function help to look at the textstring functions for right and left.

If the phone number is not in a constant format it is more difficult. I will assume it is formatted unless you say differently.

Thanks for the help. This is an example

Name Address Phone
Smith 123 Jones Street Phone goes here
(343)232-2222
Wills 123 Jones Steet Phone goes here
(232)333-3333

I hope you understand. There are 4 rows here where there should only be 2. I need to move the phone number "over one" and "up one". I can do a simple =B2 for the top one, but won't i have to do that on EVERY RECORD? That is what I am trying to avoid?
Thanks again
 

cnank

New Member
Joined
Jul 16, 2002
Messages
23

ADVERTISEMENT

hehe, no just copy the formula that works for the first cell in the column or row, then select/highlight the rest of the column or row and paste! your excel worksheet should automatically be set to relative referencing so it will manage it fine. If you have an issue feel free to email a contact number at carl.nank@jackinthebox.com
I can talk you through it. its kinda hard to communicate effectively this way.
 

davidbook

New Member
Joined
Nov 3, 2002
Messages
4
Thanks for the offer. I would not want to burden you with a phone call. I am trying to figure out what your last info meant? I have placed the statement in one box, now trying to get the other column cells to "be the same". I'll post my results.
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
As stated, in B1, enter =A2, and copy down for your 11,000 records. Of course, in B2, you will have the next records address, which you will want to discard. If after copying the formula down, you then copy the entire column B and paste special values, you can then sort the whole thing on column A. This will put all the phone numbers together, along with all the redundant names and address. You can then delete them.

HTH

Richard
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
There's no need to generate the unwanted values - you can set up a formula to do exactly what you want. In what cell / row does your data (not including headings) start.
paddy

EDIT: for example, if the name only turns up once, and there are only ever 2 rows per entry, then something of the form:

=IF(LEN(A2),B3,"")

could be used to return the number only on those rows with a name.
This message was edited by PaddyD on 2002-11-04 19:22
 

Forum statistics

Threads
1,144,363
Messages
5,723,919
Members
422,527
Latest member
JayTheKaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top