how to add a letter in front of each number in a cell

 I am working on this project & I cant figure out how to add a letter in 
front of each number in a cell without clicking in to each individual cell 
and typing it in. (very time consuming and tedious) The problem is - it's not 
all the cells but a majority, all of the numbers are in the same column. To 
explain, these are documents in storage. Each item is assigned a number (not 
in any particular numerical order but they have to stay in the order they are.

Example:

X39655
X39656
X39711
39662
39664
39665

The last three numbers need the X in front of them.


Any ideas? Thanks so much!!


I read this previous reply and I'm not getting it.......I'm Excel challenged 
:-(

Use a help formula

=Sheet1!A1&"scc"

copy down/across, then  copy and paste special as values in place ,
finally replace the old values with the new

Regards,

Peo Sjoblom


0
6/16/2005 5:24:02 AM
excel.misc 78881 articles. 5 followers. Follow

15 Replies
483 Views

Similar Articles

[PageSpeed] 18

suppose your data are in A1 t0 A6

in B4 type
="x"&A4
you can copy this down to b5 abd B6
try and see whether you get what you want.




nmodafferi <nmodafferi@discussions.microsoft.com> wrote in message
news:57F98B0B-C847-4F66-B8F2-9928492E241C@microsoft.com...
> I am working on this project & I cant figure out how to add a letter in
> front of each number in a cell without clicking in to each individual cell
> and typing it in. (very time consuming and tedious) The problem is - it's
not
> all the cells but a majority, all of the numbers are in the same column.
To
> explain, these are documents in storage. Each item is assigned a number
(not
> in any particular numerical order but they have to stay in the order they
are.
>
> Example:
>
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
>
> The last three numbers need the X in front of them.
>
>
> Any ideas? Thanks so much!!
>
>
> I read this previous reply and I'm not getting it.......I'm Excel
challenged
> :-(
>
> Use a help formula
>
> =Sheet1!A1&"scc"
>
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
>
> Regards,
>
> Peo Sjoblom
>
>


0
R
6/16/2005 6:16:33 AM
Hi,
depending on what sort of data you need to put in front of the numbers,
but two ways I can think of

say your data starts in A1

in column B go down and put the data in the cells where you want to add
something before the corresponding cell in column A.

so your data would look like this.

- col A ----- col B ----- col C
X39655
X39656
X39711
39662--------x
39664--------x
39665--------x


in C1 put the formula =B1&A1

you can then copy this down the column (click on the black square at
the bottom right corner of C1 and drag it down the column C.)

If you have nothing but X's to put at the start you could sort the data
numerically so that all the X numbers were at the bottom, then in B1 put
the formula ="X"&A1

again copy this down until you reach the numbers prefixed with the
"X".
(if you choose the second one befroe you start you may want to insert a
helper column with a number series in  say 1-1000 so after sorting and
inserting the X's you can resort back to the original list.)

HTH.


-- 
greg7468


------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9031
View this thread: http://www.excelforum.com/showthread.php?threadid=379578

0
6/16/2005 6:58:15 AM
nmodafferi Wrote: 
> I am working on this project & I cant figure out how to add a letter in
> front of each number in a cell without clicking in to each individual
> cell
> and typing it in. (very time consuming and tedious) The problem is -
> it's not
> all the cells but a majority, all of the numbers are in the same
> column. To
> explain, these are documents in storage. Each item is assigned a number
> (not
> in any particular numerical order but they have to stay in the order
> they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel
> challenged
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom



Hi, you may try this, let say the original data found in column A1. add
the below formula in colum B1 and drag down . Good luck


=IF(LEFT(A1,1) <> "X", "X"&A1, A1)


-- 
swchee
------------------------------------------------------------------------
swchee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24279
View this thread: http://www.excelforum.com/showthread.php?threadid=379578

0
6/16/2005 7:29:36 AM
perhaps repeating some info,but are you saying that some numbers already have 
the x in front and some dont,no particular order or pattern but all prefixes 
if present are x and if no x is present then an x must be prefixed?
-- 
paul
remove nospam for email addy!



"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
6/16/2005 5:25:09 PM
use  =IF(LEFT(A1)="x",A1,"x"&A1) in an adjacent cell, then copy down.  if the 
original cell already starts with an x, none is put there.
-- 
Gary's Student


"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
GarysStudent (1572)
6/16/2005 5:32:02 PM
ok if i read you correctly you need to put this formula in a column(say b1) 
next to your column of numbers 
=IF(LEFT(A1,1)="x",A1,CONCATENATE("x",A1)).This example assumes that your 
column of number starts in A1.Now copy this formula down column b to  bottom 
of your data.You should now have a column all prefixed with x.Highlight the 
whole column of new numbers and copy, go back to your original column (say A) 
and paste special>values,and lastly delete your column of formulas.....
-- 
paul
remove nospam for email addy!



"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
6/16/2005 5:34:03 PM
Hi,
If your Numbers are there in Cell A1, If you want to Add letter" X" infront 
of each number, you use formula
="X"&A1

"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
vishu (48)
6/16/2005 5:56:05 PM
This should sort it:

If you have your numbers in say column E   (just numbers - no x)
Then put an x in Column E    next to numbers that don't have x's in E

Then in column F write a formula that says:

=LEFT(D8,1)&E8

this will join the 2 columns up.    The nuber 1 after D8 just says take the 
1st number. If you had e.g. the name John in the column and your sais 2... it 
would take the first 2 letters i.e JO

hope this helps




"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
PhilWales (15)
6/16/2005 6:05:04 PM
=if(isnumber(a1),"X"&A1,A1)

"vishu" <vishu@discussions.microsoft.com> wrote in message 
news:CF09FDEB-602A-450C-8D46-237DC5A2ED48@microsoft.com...
> Hi,
> If your Numbers are there in Cell A1, If you want to Add letter" X" 
> infront
> of each number, you use formula
> ="X"&A1
>
> "nmodafferi" wrote:
>
>>  I am working on this project & I cant figure out how to add a letter in
>> front of each number in a cell without clicking in to each individual 
>> cell
>> and typing it in. (very time consuming and tedious) The problem is - it's 
>> not
>> all the cells but a majority, all of the numbers are in the same column. 
>> To
>> explain, these are documents in storage. Each item is assigned a number 
>> (not
>> in any particular numerical order but they have to stay in the order they 
>> are.
>>
>> Example:
>>
>> X39655
>> X39656
>> X39711
>> 39662
>> 39664
>> 39665
>>
>> The last three numbers need the X in front of them.
>>
>>
>> Any ideas? Thanks so much!!
>>
>>
>> I read this previous reply and I'm not getting it.......I'm Excel 
>> challenged
>> :-(
>>
>> Use a help formula
>>
>> =Sheet1!A1&"scc"
>>
>> copy down/across, then  copy and paste special as values in place ,
>> finally replace the old values with the new
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> 


0
N
6/16/2005 6:17:25 PM
if X is the character which needs to go in front of all data without an X

use a helper column ( a column which you can delete later with out losing 
any current data)
 for example if your cells are in column A I wuld insert a column B.
in B1 (or whatever row has the initial data) enter
=if(left(A1,1)="X",A1,"X"&A1)
copy this down to the bottom of your data.
copy these cells and paste special values over the corresponding Cells in 
Column A.
Now delete Column B


"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
BJ (832)
6/16/2005 6:33:06 PM
Try using the formula = LEFT(D3,1)&E3    for example.
U will have to put an in the column next to your data numbers 

this formula will then combine an x with your number



"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
PhilWales (15)
6/16/2005 7:14:07 PM
this may be a second response
Insert a new column B
in B1
=If(left(A1,1)="X",A1,"X"&A1)
copy to the bottom of the data
Copy the data in column B and paste special values over Column A
Delete Column B

"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
BJ (832)
6/16/2005 7:22:05 PM
Assuming that you want to add X as the first character of every one, you can 
use this formula where A1 contains the numeric (39662).

="X"&A1

If the first character resides in B1, you could change it to

=B1&A1

"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
BarbR (262)
6/16/2005 7:47:03 PM
Hi,
I believe that the SAME letter ("X") has to go in front of those numbers.  
Let's imagine that Column A contains the data, starting with Row 2.
Create a dummy column in any empty column (let's say Col B) with the 
following formula:

In cell B2 enter the formula,
=IF(LEFT(A2,1)="X",A2,"X"&A2),
and fill-down Col B down to its last row.
Col B should have the modified data.
Now select the Col B, click Copy, then click Edit-Paste Special-Values.
If you want, you can now delete the original "flawed" column (i., Col A) and 
move the modfied values in Col B to Col A.

Regards.
B.R.Ramachandran
 

"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
6/16/2005 7:49:04 PM
First, save your data and use a copy for this exercize.....

Assuming your data is in column A, and column B is blank....in B1 put this 
formula and copy down as far as you have data in column A......

=IF(LEFT(A1,1)="X",A1,"X"&A1)

You can then highlight the column B and do Copy > PasteSpecial > Values to 
get rid of the formulas.........then, you can delete your original column A 
if you wish.

Vaya con Dios,
Chuck, CABGx3





"nmodafferi" wrote:

>  I am working on this project & I cant figure out how to add a letter in 
> front of each number in a cell without clicking in to each individual cell 
> and typing it in. (very time consuming and tedious) The problem is - it's not 
> all the cells but a majority, all of the numbers are in the same column. To 
> explain, these are documents in storage. Each item is assigned a number (not 
> in any particular numerical order but they have to stay in the order they are.
> 
> Example:
> 
> X39655
> X39656
> X39711
> 39662
> 39664
> 39665
> 
> The last three numbers need the X in front of them.
> 
> 
> Any ideas? Thanks so much!!
> 
> 
> I read this previous reply and I'm not getting it.......I'm Excel challenged 
> :-(
> 
> Use a help formula
> 
> =Sheet1!A1&"scc"
> 
> copy down/across, then  copy and paste special as values in place ,
> finally replace the old values with the new
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
0
CLR (807)
6/16/2005 7:58:19 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

VBA to check for latest version of front end?
I have a split database where the users have a local copy of the frontend. I would like to use VBA to check whether the users have the mostrecent version. I have set up the following tables:tVersion-Back-End (link to table in the BackEnd version)tVersion-FrontEnd (local table in the FrontEnd version)Each has a field Version where I am planning to put in the versionnumbers.Now, I am just starting to use VBA in my Access projects. Can someonepoint me to some coding to accomplish my task above? Can someone alsopoint me to a good resource (book, website, course) that would take mefrom a beginn...

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

Add an Active Directory Object to Organizational Unit !!!!
Hi everyBody , I want to add an Active Directory Object (User Acount , Group , Compturer...) to an OU by using PowerShell , and i don't know How , any help will be apreciate . thanks . use the redirusr or redircmp from microsoft. It will always go to that OU. "mamhil" <mohamedtawfik@hotmail.com> wrote in message news:BAC9EB20-A4B6-466B-96A9-587B645A18F5@microsoft.com... > Hi everyBody , I want to add an Active Directory Object (User Acount , > Group , Compturer...) to an OU by using PowerShell , and i don't know How > , any help will be ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Add PowerPoint 2007 to Office Basic
I have Office Basic installed on my laptop and need to add PowerPoint. What is the most recommended, easiest and least expensive way to do this? ...

how many receivers i can add
i want to use outlook send 2000 thousands emails to 2000 peoples at the same time. can i put them all in the recivers and send them at one time? thank you for you help ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Roman and Arabic numbers in SEQ
Running 2003 on XP. Got a doc that has SEQ numbering. The code is: ARTICLE{seq level0\h\r0}{seq level1\h\r0} etc. to level 7 and the last entry is {seq level0\r1\*ROMAN) This displays ARTICLE I The next level down is has this code: {seq level0\c\*ROMAN}.{seq level1\r1 \*arabic} This displays as : I.1, with numbers until article five displaying as V.1, V.2, etc. I want it to display as 1.1, 1.2, etc. I tried replacing the ROMAN with arabic, but it didn't change anything. I have spent hours trying to figure this out. Using reveal formating and styles, they are no styles...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Auto calc YTD Numbers
Cells A1:L1 equal "Jan" thru "Dec". M1 equals "YTD". Cells A2:L2 equal numeric values. If you haven't guessed it already, I would like M2 to sum all the values A2:L2 for those months previous and including the current month (but not future months). What is the best way for going about this Here are 2 ways =SUM(OFFSET($A$2,,,,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0))) or =SUM(OFFSET($A$2,,,,MONTH(TODAY()))) hopefully your computer's clock is accurate -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjo...

Sum a group of numbers
When I first started using Excel, if I had a bunch of different totals on a worksheet, I could hold down the right mouse button and cover the group I wanted to add. The sum of these numbers would appear on the bottom of either the worksheet or a task bar. I have lost the ability to do this. Or I have lost the task bar. How do I get it back? Hi Barbie, That would be the status bar. If it is not showing, go to View>Status Bar to turn it on. If the sum function is still not showing, right click anywhere on the status bar and select which option you want. HTH Martin "Barbie...

IM Add New Batch
GP 10 on 2003 Terminal Server. How do you create a Payables Batch in the IM in preparation for importing to the Payables Transaction destination? Alternatively, how must GP be set up to ensure that the “Add New Batch” rule works correctly? With the Add New Batch rule set the IM crashes (see my post Integration Manager 10 Crashes). Additionally the characteristics of the Add New Batch rule do not match the required information which I understand to be Origin and Checkbook ID. I figured there might be some default settings for Origin and Checkbook ID in the setup somewhere but was not a...