Phone Number Problem

  • Thread starter Thread starter D3Stusi
  • Start date Start date
D

D3Stusi

I am creating a mailing list, in which all the phone numbers have th
same area code and firs 3 digits. So i created a formula as follow
=A1+3418930000 now all i have to do is enter the last 4 digits into a
and it outputs the phone number (which i have formatted so it comes ou
looking like one). In any case, my question is, how do i make it s
when i copy and paste this formula, it follows the cells. When i cop
and paste it right now, i have to change the formula to reflect wha
row i'm on. So row 2 i would need to change it to =a2+3411890000. I
there a way to do this automatically? I want to be able to past thi
formula all the way down my sheet and just have it reflect the ro
number in my formulat. So row 3 would be =a3+etc... row 4 would b
=a4+etc... I have no idea how to do this, any ideas? Also if there i
an easy way to do what i'm trying to do, please lemme know
 
I'm still learning also but, if i understand your problem, your goin
about it the wrong way.

your formula adds the value of a1 to your phone number.

i think a better way would be to use a VLOOKUP function, and also
RIGHT function

ex
=vlookup(a1,phone,1)

a1 is the last four that you enter, phone is your range of phon
numbers, and the 1 is how many cols over.

well, you need to modify that to look only at the last four, and phon
numbers do not need to be added, you can treat them as text (forma
cells to text)

=RIGHT(text,num_chars)

so 555-444-9999

=right(555-444-9999,4) would result in 9999

both functions are in the help file to help you out if need.

this is just a general idea and not an answer to your questio
 
The formula you want is =845437&A1

This is presuming that you are inserting the 'last 4' in the A column.
Essentially, it sounds like you're putting in the extension numbers for a
company's phone list and simply adding the area code and prefix for all of
them.
The ampersand (&) is used to do just what you are looking to do. I have
used it to consolidate names into full names...City, State and zip codes
etc. The proper name of it is CONCATENATE which you should be able to look
up in the Help system.
Actually, I just tried your way and it worked. The way I 'copy and paste'
is by using the AutoFill handle.
With the formula in B1 and B1 being the active cell (selected cell) move
your mouse pointer to the lower right corner of B1 until is changes to a
black cross. When it changes to a black cross, click and drag down to
'copy' the formula. The cell references 'should' change. This is called a
relative reference. This means that because the row number changes, the
formula changes to reflect that change. Lookup Relative Reference in the
Help system.
I would avoid the use of the plus sign (+) in this type of formula since it
could give a false result.

Last but not least...if you are using an OLD version of Excel it may not be
recalculating automatically. Anything from Office 97 forward automatically
recalculates everything.

Sorry so wordy but once I get going...
Any other questions feel free to ask away.

Dave French
Office 97 Excel Expert certified
 
On way,

- enter your formula in, say, B1.
- select B1
- position the cursor over the little black square in the bottom-right
corner of B1
- drag down as far as necessary

or,

- enter your formula in, say, B1.
- select B1
- shift-select the cell in column B on the row of the last cell in column A
- Edit>Fill>Down or Ctrl+D

When you're done you can select column B and do Copy>Edit>Paste Special>Values
to get rid of the formulas.

HTH
Anders Silven
 
Hi
why not apply a custom format to these cells like
"341893"0000
and just enter your 4 digits
 
Nice approach, Frank,

The OP doesn't specify the exact format but maybe something like
"341-893 "0000

Best regards
Anders Silven
 
Back
Top