Hey everybody.

I don't know if the title is a good one for this problem, but here goes.

I have a column that has numbers in this format: 54321-1234

Now, I want to take off the last 4 numbers, and the dash, from all the numbers in the column, using a formula. So that the output would be: 54321.

I want it so the first 5 numbers stay the same, but the dash and last 4 are gone. Is this possible?

Thanks.

First time posting...I am working on a spreadsheet for work in calculating a benefit. I have listed in C13 a minimum benefit (currently $25, but it varies). Later in the spreadsheet I subtract two numbers (c32-c33). If the number is less than zero, I have the formula put 0 in C34 as the answer IF(C32-C33<0,"0",(C32-C33)). That works fine. The problem is that later I when I want to list out what to pay, if the final answer is less than the minimum, I want the minimum to show, otherwise I want the calculated answer to show. Here is my formula. IF(C34<C13,C13,C47) Everything works great unless the original numbers subtracted is less that zero....for example $600 - $600 - the final results spits out the minimum of $25. Perfect. But when $600 - $601 = really equals -1, but I have it showing 0, the final answer spits out 0, not the $25 that I want. Any ideas on what to do to get it to put the C13 value instead of 0? Thanks so much for the assistance!

I've tried copying/pasting into Notepad; formatting cells into every format possible; have tried making another column, entering the =VALUE(A1) formula, and copying/pasting down the column; have tried Text to Columns; and find/replace $ sign...

When I export my data into Excel, I am unable to sort correctly and therefore, am also unable to Autosum.

Sorting results in:

$1,000.00

$110.00

$12.00

$50.00

Instead of by true value:

$12.00

$50.00

$110.00

$1,000.00

Please help! thanks in advance

*note, attached doc is just a sampling of the several hundred records from the exported report

Excel 2003, Windows xp

Peace be upon you

1- please apply this function =VALUE(TRIM(MID(B1,2,LEN(B1)-2)))

2- Pull down to all your values

3- mark Solved

Thank you and best regards

I am exporting report data from an accounting program into Excel 2003, then having to change some of the worksheet data into to a specific format (some columns show dates, phone numbers, etc - which need to be converted to text - translation:HEADACHE) and then saving file as ".csv" to submit reports online to another agency. I am pulling my hair out here because it keeps changing to funky formats in the .csv sheet and I find myself back at square one. I am required to do this on a monthly basis, and will soon be submitting a similar file to a different agency, so I will double my headache if I can't get my head wrappred around this.

Any help would be great!!!

Thanks.

I'm running XP Pro sp3, Office 2003

i have used the code below, so that when i enter a number in cell e7 a running total is shown in h10, and all figures entered in cell e7 are shown individually in Column L, is it possible instead of entering a number manually, can the number be generated randomly when a command button is pressed, i would need numbers generated randomly between 0 and 50

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WB As Workbook, ws As Worksheet, wsk As Worksheet

Set WB = ThisWorkbook

Set ws = WB.Worksheets("Sheet2")

If Not Intersect(Target, Range("E7")) Is Nothing Then

ws.Range("H10").Value = ws.Range("H10").Value + ws.Range("E7").Value

x = WorksheetFunction.CountA(Columns(12))

Cells(x + 1, 12) = Target

End If

I've been trying to write a simple formalue for Excel to generate a random number in a cell when I press the 'enter' or any other key. I've been trying with using =randbetween(1,100)

Any tips please ??

thanks

Can anybody help with this question? I have a massive spread sheet with numerical data in it. on paper I have associated a number to different ranges.

For example in columb A any number that is between 25-30 needs to be converted to 0.375. Can anyone tell me a quick way to convert these and other numbers in one go?

Hello,

I am having trouble with Excel 2003 automatically adding the last digit in a Postal Code. For example, when I type in "R3R 4K5" in A1 and then click the corner of it and drag it down a couple of cells it goes from "R3R 4K5" to "R3R 4K6" and "R3R 4K7" and so on.

Is there an option that you can disable to prevent this?

Thanks!

Here comes the dumbest and easiest Excel question ever, folks....!

In 1 column I have 8-digit numbers like 73269090. I need them all to take on the format xxxx.xx.xx (7326.90.90). For the life of me I cannot figure out how to accomplish this in the "Custom" menu of "Numbers".

Who will help the most ignorant Excel user ever??

Cheers [email protected]

This was asked before by KlausdB:

"I have 4 x HDDs on SATA AHCI (Intel Controller) in Native Mode and 1 x ESATA on Win 7 Pro X64.

I noted that looking in Admin. Tools\Computer Management\Storage (as an example) the 1st HDD (Port 0 in the AHCI BIOS) is showing as "Disk 2" instead of my expectation of

"Disk 1".

Selecting the Drive's Properties correctly shows the "Location 0 (Channel 0, Target 0, Lun 0)".

I also note that the Disk Number allocation in Windows will change randomly after some reboots/startups - going anywhere from "Disk 1" to "Disk 4". I can force/replicate this

behaviour by removing the ESATA drive.

Is this expected behaviour?

It doesn't appear to create any operating issues at this stage, however, is there a way I can force the Drive to always map to (eg) Device 0 --> Disk 1, Device 1 --> Disk 2,

etc?

Thanks."

This was answered by Nicholas Li:

"As far as I know, how the disk is plugged physically and the order it is displayed are not related. For a simple example, if a drive is plugged to SATA interface 4 and it is the only disk, it will be displayed as Disk 0. If there is no related issue about

the disk order, I think we can safely ignore this."

Because the topic was closed I want to ask a new question here:

What happens if one of the 4 HDD drives or 5 has an OS installed on it? I ask because windows always write some bo... Read more

I have three columns in Numbers (Column H, J, and R). Columns R is an exact copy of Column H.

Every time a certain value appears in Column H, I need the corresponding value in Column R (but not Column H) in the same row to become negative.

Now, I have over 100k rows that I am working with, so manually changing seems to be out of the question.

How exactly can I do this?

Hi Eraserboy, welcome to TSG.

You don't say what program you're using, but I assume Excel? If so, you can use an if formula. You don't say what value you need to find in column H, but I'll use 5 as the example. You'd put this formula in R1 and drag it down the rest of the column.

=IF(H1=5,-5,H1)

The formula says "if the value in H1 is 5, then make R1 = -5. If not, make it the same as H1". Replace the 5 and -5 with whatever value you need.

Hope that helps.

Hi,

Can anybody guide me to generate continous numbers from 1 onwards while I filter the records? I need to generate these numbers in first column in order to know how many records are available after filtering.

regards,

Abdurazik

[email protected]

I have an Excel workbook that prints many pages. With this workbook, after the 7th page I insert pages from a different program, and then the rest of the Excel workbook pages follow.

The Excel workbook is actually 20 pages long.

Example:

I need a way to create a macro that will insert 1 of 20 pages on the first seven pages of the Excel workbook pages. Then since 2 pages from another workbook will be added before the other 13 Excel pages, I need the numbering to reset to show 10 of 20.

Does this make sense? Can this be done?

I am trying to create a template in excel with the name, address, and number repeated three times on one page and then when the template opens again I would like it to assign the new name the next sequential number so that no numbers can be skipped or used twice. See below. Can you help? Thank you in advance.

Jane Doe

1 Main Street

Anytown, Fl

1101

Jane Doe

1 Main Street

Anytown, Fl

1101

Jane Doe

1 Main Street

Anytown, Fl

1101

Hi,

I currently have data that is pulled from a database with a collumn which represents the time a call was logged. However, the information in the cell shows as 130237 when it should show as 13:02:37. How can I manipulate this and also be able to change the formatting to time. Currently if I try and format it, it shows as 00:00:00

Cheers

Thats coming in as a full date - so

130237 is the date

28/07/2256

if you change to a time format and also use value - then it will be seen as a time

assuming the time is in cell G8 and you always get leading zeros - which maybe a problem for 9am

=VALUE(MID(G8,1,2)&":"&MID(G8,3,2)&":"&MID(G8,5,2))

I keep a monthly budget record of our expenses in worksheets labeled by month. There are 13 worksheets in a file (1 for annual total). I have row labels such as "wine, food, our cats, etc" & enter data from our joint account, as purchases are made, into each cell. E.G.: B12 might be: =34.66+73.45+2.68 etc. At the end of the month I "proof out" our account to reconcile it with my bank statement. There are always missed or multiple spreadsheet entries that make the excel total different from the banks & I have to find them. I use online banking & my probem began when the bank "upgraded" their online service & no longer allowed me to sort the debits by amount. Before they did this I could look at the excel cell entries & find them very quickly on the banks sorted printout & check them off. Since I can no longer sort the banks data it has become much more difficult to do this monthly chore. Is it possible to sort the multiple number entries in each cell & display them in a column? BTW: I don't mean to sort within the cell, but all cells contents together for display, so it will look like the banks used to look.

Any help really appreciaced.

Ron40

Hello,

I was wondering why numbers show up that way and what it generally means.

I can double click each cell in the column and it will show the real number i need, such as:

634479009815

Is there a way to convert the whole row, so the numbers will show normally.

this should be just a general format.. this is just a generic id, and not currency or any other special format.

Thanks.

I use excel 03/07 both.

Code:

B C D

time data result

12.00 1

12.05 1

12.10 1 3

12.12 - 2

12.25 -1 -3

12.40 1 1

12.55 -1 - 1

1.05 1

1.16 5 6

hi

can any body help in making a macro that sum the same signs numbers in column C and put the results in in column D as in the examples above

and every time that i start the macro ....it will start to calculate from the first cell D2 and go down

hope to find this macro

My tools: Dell dimension, circa year 2000. Windows 98SE, OE, IE6. I do not use Outlook: too complicated.

Yesterday and today large numbers of virus-bearing messages have arrived by email. They originate from a variety of foreign countries-uk, Japan, Russia, etc. Almost all purport to be from Microsoft, or an announcement such as a message that did not go through. Yesterday, 57 of them. Today in 20 minutes since I logged on, 30 and counting, with many more indicated from the server of my regional dial-up provider.

Fortunately Norton antivirus and firewall have identified all thus far. Of yesterday's crop, all the viruses were known to Symantec and none needed to be sent in. All, of course, first quarantined and then deleted.

It does take a long time to wait, screen out the wanted email (I receive from 10 to 20 per day normally, with 0-3 bearing noxious material), and is a stress as well as a nuisance. I have on each message clicked on my OE toolbar Message, then Block sender. But still they come.

Have I any other way to get rid of this material in advance? It would be nice not to have to sit here like this.

Thank you for any help you may offer.

You can set a message rule to have them all go into the trash folder instead.

I have three SATA hard drives :

Main system/boot drive , WD Caviar Black 1 TB, connected to the SATA 0 port

A backup drive, identical WD Caviar Black 1 TB, connected to the SATA 1 port

Another dirve, Hitachi 1 TB, connected to the SATA 2 port

When I installed Win 7, only the main drive was connected... and it remains the only drive with active/system/boot partitions.

QUESTION 1.

All three drives reporr correctly in the BIOS, and in the right order, but why is it that only one of the drive (the one on SATA port 1) has a "SATA:" prefix and the others have a "HDD:" prefix ?

QUESTION 2,

Why is it that the assignment of Disk #0, 1, 2 ( as seen in Disk Maanger or DiskPart) does not seem to have nay rhyme or reason?

I would have expected:

- the hardrive connected to SATA port O, to be assigned as Disk 0

- the hardrive connected to SATA port 1, to be assigned as Disk 1

- the hardrive connected to SATA port 2, to be assigned as Disk 2

but instead, I have:

- the hardrive connected to SATA port 2, is assigned as Disk 0

- the hardrive connected to SATA port 0, is assigned as Disk 1

- the hardrive connected to SATA port 1, is assigned as Disk 2

Just curious why that is.

I am assuming there is no way to chagne this. Its not something I would dream of reinstalling Windows 7 to correct ( even if it did correct it).

Best advice, leave it alone. It may bother your (and others) sense of order but it won't affect how anything works, and no re-installing Windows won't change it.

9 more repliesI’d like to set page numbers at 7.2″, .5″ below the top edge of the page

The page numbers tab provides these options only:

Position:

Top of page

Bottom of page

Alignment:

right-left-center-inside- outside

Is it possible to set margins for page numbers in word documents? And how?

I believe this may be what you are looking for:

Step 1: Use what you have already described to insert page numbers as a header.

Step 2: Click on View.

Step 3: Select header and footer.

Step 4: Left click on the page number so that a little grey box contains it.

Step 5: Scroll over the little grey box containing the page number so that you get the icon that lets you move a selected item. The icon looks like the xy-axes from algebra.

Step 6: Hold down the left mouse button and move the page number to any place on the page you want it, even outside of the header. This will affect all page numbers on all pages. When the page number is where you want it, release the left mouse button.

Step 7: Close the header and footer box.

Hope this helps.

in all applications as9text is entered a 9 will pop in at random times, 9ee above, see above. Files sent to another laptop have also corrupted it with an alphabet of abcdefg51236*nopqrstuvwxyz.

Where ids the problem?

First, Thanks for looking !

I have to create a spreadsheet with numbers that repeat for merging into another program. The number sequence is:

1001

1001

1002

1002

1003

1003

1004

etc.

When I type in a sequence of them and then drag and fill I get the decimal versions after the original set I typed in, which since it is rounding, tends to jump or repeat a sequenced number:

1004

1004

1005

1005

1005

1006

etc.

How do I get it to repeat the number twice, but not skip or repeat based upon the rounding of the decimals that are hidden?

Thanks again!

Hi

I am using office 2000 and windows 98. I just had it all re-installed and now when i open up an old spreadsheet it shows the negative numbers just with the minus sign infront of the number. Where as before all the negative numbers would automatically appear in Brackets.

Does anyone know how to change this back?

It's hard to come up with a snappy title that sums up my problem but I'm hoping it will be fairly simple to solve.

I've created an IF formula in an excel spreadsheet and I want to drag the cell across a few more cells. The formula will reference the same cell so I can make that an 'absolute reference' but then I want the logical test number to increase by 1 each time and the value if true to remain the same.

=IF($A$1>0,1) [in the first cell, then in the next to the right]

=IF($A$1>1,1) [then]

=IF($A$1>2,1) [and so on]

Can anyone please tell me if this is possible? and if so, how??!!

heres hoping,

Nellie.

I have used MS-Excel for many years and am fairly familiar with with using formulas. However I am a total novice when it comes to creating and using Macros. I am using MS-Excel 2003 and have run into a requirement that I don't think can be handled with just formulas.

I need to create several arrays of random integers. The number range and the quantity of numbers is determined by numbers entered into other cells. So far, I could do that with formulas. The catch is that each array can not have any duplicate numbers in it. There also needs to be an error check to make sure the number range is equal to or larger than the quantity of numbers needed.

I have attached a file that shows the workbook layout and includes examples of the type of arrays I need to generate. All help and guidance is welcome and will be appreciated.

Hi All

I have a spreadsheet full of numbers formatted as currency. Some dollar amounts are positive and some are negative. I want all the negative numbers to be in red font. Instead of me searching for all the negative numbers (they are in the hundreds) and turning them all red manually, is there a simple macro that can do the trick? This will save me a lot of time. Since the numbers change from negative to positive and vice versa and the number of rows and columns also change, I'm having a hard time programming it.

Has anyone out there written a macro than can do this?

Thanks

Mario

It's built into Excel - just highlight the cells, go to format cells.

Go to Currency. There are 2 boxes on the right - the lower one allows you to have negative numbers in parentheses ($3.00) or red $3.00 or a combination of those two or with a minus sign.

Hello group,

I wasn't quite sure how to word the subject line. I would like to be able to add a number to column A everyday and have the cumulative sum of column A show in column B in the adjacent cell so I can chart it. So it would look something like this:

1 1

1 2

1 3

2 5

-1 4

I think it's something to do w/the SUM function but I can't quite figure it out. Any help much appreciated.

Harold

I was checking my program and software list and at the top of the list I noticed two (2) sets of numbers that I have never seen before. I checked the internet to see if I could find out what they were. It kept taking me to sites for spyware, adware,etc. I could not find out what they were or why they are on my system. I do not want to remove them until I know what they are. I have not noticed any problems with my system, but I do not know what might be going on behind the sceen. The numbers are listed as follows:

{684CB795-C157-4E15-93D4-E26015FEF1EA}

{F850707C-B6A0-4B56-8709-F89CF8F9AC6D}

ANY HELP?

The first one > http://www.acresso.com/products/is/i...d-overview.htm

The second one > http://eraser.heidi.ie/

Both are legit and not malware.

I have Windows XP, with Wordpad v5.1 and Notepad v5.1 as well. In Notepad, if I toggle off Word Wrap, I can then toggle on the Status Bar, and it shows me the line and column number in the status bar. It does not look like Wordpad has the ability to show line and column numbers. Does anyone know if there is a way to show that in Wordpad? Thanks.

More repliesIn my past I worked in a music store and took a handful of cd's home everyday to put in my computer, most of them are .WAV about 40 or 50K though I do have 15 or 20K MP3's. Is there a program available to sort through all this mess, and maybe help me remove some of the doubles I have recorded. It's becoming a nightmare to deal with, I have 8 250GB usb drives full of music, I figure if I rip the wav's to MP3 I can put the songs on just on or two harddrives, I just need some software that will let me do it in an orderly fashion. Any help will be really appreciated.

you could probably use itunes or windows media player etc to convert them to mp3 format, then sort the library by name/albums, and delete duplicates from there, but makeing sure you click on 'remove from library and computer'...

although keep a backup of the original files while you figure this bit out from the programs just incase it deletes everything there, then delete them when its all good...

and make sure its only reading from teh one folders/drive

I'm using VLookup & need my reference numbers on the current and the lookup pages to have that left apostrophe at the beginning of both sets of reference numbers or otherwise to have no apostrophe there at all. Without consistency I can't perform the lookup.

Is there a quick way to add or remove those apostrophes in a long list of numbers?

Howdy,

I have an Excel document with hundreds of values all with leading zeros.

The number of zeros varies from cell to cell, and the cell lengths may vary, so I might have something like:

00000123

000000012004

00555555

00000000000000ABC

00DEFGHIJ

I need to remove the leading zeros from all cells.

I can't multiply by the values by 1, because of the non-numeric entries.

I can't Replace All zeros, cos it would replace any zeros that appear within the codes, not just the leading ones.

I can't do a Replace All searching for * then a specific character, because the first char of the code is not specific.

So I'm thinking I need to do something like:

=RIGHT(A1, FIND(<>"0",A1,1)-x)

Obviously that doesn't work, cos it doesn't like the <>"0" part, and I can't put a set number in place of the "x" at the end as the number of characters to return differs from cell to cell.

Or maybe use the SUBSTITUTE function somehow, though again I still need to tell it to stop when it hits a char other than zero, as =SUBSTITUTE(A1,"0","") applied to line 2 in my example data above would return 124 instead of 12004.

Thanks!

Hi Gram. I checked ASAP Utilities but couldn't see this covered.

Sub Macro1()

For Each Cell In Selection

x = Cell

If Left(x, 1) = 0 Then

Do Until Left(x, 1) <> 0

x = Right(x, Len(x) - 1)

Loop

End If

Cell.Offset(, 1) = x

Next Cell

End Sub

Whilst my daughter was playing with the computer yesterday she downloaded something that has introduced a malware that has stopped AVG from working and has shut me out of turning it on, starting a scan or updating. Additionally it has introduced large numbers of pop ups (many of which are dodgy in the extreme). Any help would be greatfully recieved.

I should add that I have searched through the hidden files folder and not found anything suspect and wonder if the combo fix software is the way forward?

14 more repliesMy page numbers appear fine in the slides but don't appear in Print Preview and will not print up????

if you goto

insert

slide number

and tick slide number there

Mine is on print preview OK

In Excel, when one has a scatter diagram, and values such as 9.81, 19.62 and 29.43 on the x-axis, how can one get Excel to display the decimal numbers on the x-axis, rather than have 9.81 be set a little before 10, 19.62 be set a little before 20 etc. ? thanks,

Christian K.

Try building a line chart first using just the y values.

Then select the range on the chart -- you'll get something like

=SERIES(,,Sheet1!$B$1:$B$3,1)

in the formula bar. Click between the 2 commas, then click & drag on the x values (on the worksheet).

If you *must* stick with scatter, try Rob Bovey's free Labeler add-in at http://www.appspro.com/utilities/utilities.asp

Rgds,

Andy

Hi

Please can someone help. I am trying to convert a list of 16 digit numbers from text into general so that I can do a v-lookup from another spreadsheet.

I am highlighting the column where I want to paste the numbers and formatting as custom with 16 #'s, pasting my original data into a notepad and pasting it into the new pre-formatted column.

Annoyingly, Excel is changing 4476707465712132 into 4476707465712130 which is driving me mad! I've tried using a new spreadsheet and lots of other things and it doesn't help. Now realised if I just randomly enter 16 digits into a cell, what displays after I hit return isn't what I've typed.

Please help!

Thanks

Abby

see here

http://support.microsoft.com/kb/65903

only retains 15 digits

Hi recently when I work in Excel I cant use the the numbers on the right side of my keyboard when numlock is on I can use the top row of numbers above the tab,q,w,e,r,t,y number keys just not the keys on the right side. though I ca use it with notepad is there possibly a setting I can change within excel or has excel become corrupted? Please this is extremely urgent!!!

I know by formatting the cell you can select negative numbers to appear in red. I was wondering if there was a way to do that with zeros?

Hi there,

Yes, there is, but you'll need to do a custom format as there are no default formats for showing anything equal to zero in red. Now in order to do this, you'll need to understand a little of custom formatting. There are four items you can specify, and they are in specific order: Greater than zero, less than zero, equal to zero, and textual values. These items, are separated by a semi-colon ( ; ). So something like this...

Code:

0.00;-0.00[Red];0.0[Blue];[Green]

This should explain them a little more...

http://office.microsoft.com/en-us/excel/HP051995001033.aspx

http://www.ozgrid.com/Excel/CustomFormats.htm

http://www.exceltip.com/st/Replacing_Zeros_with_Dashes_in_Number_Formatting/66.html

HTH

Hi all

When I enter a phone number into excel it chops the 0 off from the start. I've checked in

formatting but can't find the option to stop that.

Could anybody help me.

Thanks in advance.

I'm currently having a problem regarding random number generating, as I can't seem to find the answer anywhere. Anyway, my problem is that I am generating random numbers from 1 to 55, however, the probability of each number being chosen is already given. I know that if you use randbetween function, that the probability of all numbers being picked out is equal (in this case, each number has 1/55 chance of being picked). My problem is that I am already assigned some probability for each number to occur (like 5% for 1, 7% for 2, 25% for 3, 2% for 4, and so on...), and the random number generation should be based on that list of probabilities given to me. How do I do that?

Please do forgive me as I am an excel newbie, and I have utterly no idea what I'm supposed to do next.

How do I spell out numbers in Excel, i.e., twenty five dollars and fifty cents. Is there a macro out there that can do this?

Actually, there is.

You can download this sweet thang here for free (it's ALWAYS been free):

http://www.asaputilities.com

or just do a search on the web for "convert numbers to text Microsoft Excel"

They're all over the net...

I have an excel spreadsheet (2002) Someone has entered in all the numbers that are supposed to be negative numbers like this 14.56- when you try and add all the numbers all together it returns a zero value because excel doesn't understand that it is a negative number because the minus sign should be on the other side of the value.

Does anyone know of a good idea on how to get all the - signs on the other side of the number or any quick way to make excel understand these numbers as a negative eg -14.56????

Help!!!

I'm about to start working on the side doing some basic updating of ISBN's on a management site. I'm trying to hash out the issues I may encounter before I give a definite yes since it's family that I'll be working before. Here is my potential issue:

Say you have these two numbers:

1.) 0012345678

2.) 0000023456789

I know the way to format it so that the two zero's appear in front on a 10 digit number but what about when you would have 10 and 13 number ISBN's in the same excel sheet? If I were to do this:

1. Right-Click-> Format Cells

2.) Custom-> Type-> 0000000000

That would take care of any 10 digit numbers but it would make the 13 digit number lose some of the zero's.

Any ideas? I am not an Excel expert because I deal more with general repairs and deployments.

Thanks!!!

is it worth changing all the 10 isbn to 13 anyway - with this info

http://ndpsoftware.com/isbn.php

heres the formula

http://www.nationallibrary.fi/publishers/isbn/revision.html

Somehow I converted a Excel spreadsheet from numbers to a bunch of Hieroglyphics that look something like this "<À à. Any idea what I did and how I can get this spreadsheet back to a readable form? Or should I say a into a form I can read.

Thanks Sue

I'm doing quite a long spreadsheet in Excel and need to number each row sequentially. I know there used to be a way to do this but maybe it was in an older version of Excel. I'm currently using Excel 2002. I tried using a forumla to add one to the row number above but realized when I sort the sheet by a different column, the row number changes. If anyone can tell me how I can number the rows without having to hand type each number, I would appreciate it.

Hello, I am currently on Vista and using outlook 2003 as my email. On certain emails where people have put pictures, docs, etc IN the email, i get a bunch of letters and numbers. Can anyone help me with this?

Thanks.

Hi,

I’m having a problem with page numbering in Word 2003. In a document with several sections the page numbering is some sections doesn’t begin with page #1. For example, the last page of section 5 appears as, “5-10” which is correct. Unfortunately, the first page of section 6 begins, “6-11” instead of ‘6-1”. I’ve tried to format the numbers in the footer by selecting "Start at" page #1 but to no avail. Even when I do this it automatically reverts to “6-11”

Any ideas you have on have to fix this would be greatly appreciated.

Thanks.

Glenn.

I am creating event tickets and would like to add consecutive #s for raffles. I started with a business card template. I tried to use the page #s in a footer, but each page of tickets carries the same # on all tickets.

I am not very experienced in Publisher at all, and don't know how to create a macro (saw that suggested on the Microsoft website for gift certificates).

Is there a simple solution to this?

Thanks for any help.

Carol

I have done this in Publisher 2003. You also have to have Excel and first create a file in Excel. That is then linked to Publisher.

This site may help

Sequential numbering of tickets | Microsoft Office Forums

I work for a sports publication, and we deal with wins and losses, such as 3-2. When I generate this in my database and paste it to Excel, SOMETIMES it will give me a date like March 2 instead of the 3-2. It only occurs to some of the numbers and I have tried formatting to text before pasting and trying all the Paste Specials. Any ideas how I can keep them consistent? Thanks.

Hi,

I have disabled every auto-correct and auto-format option I can find, but Excel still won't stop changing my numbers to dates. If I type 1-2, it automatically changes it to 2-Jan, and so on. I'd really appreciate any suggestions you can offer me. My bio grade appreciates it, too. Thanks.

Have you tried formatting the field(s) to text?

Several times tonight I have tried to figure out why some numbers such as "154800019E00028"

Excel will convert it into some other format such as 1.54800019E+36

I have tried various ways for formatting the cells such as General, Number and Text.

But neither of the three will correct this.

Exactly what kind of number is "154800019E00028" ? The "E" in the middle usually suggests scientific notation where the number before the E is multiplied by 10 raised to the power after the E. In your case, Excel sees your number as 1,544,800,019 x 10^28. It then normalizes the number by changing the power of 10 so that the number ahead of the E is between 1 and 10. Going from 1,544,800,019 to 1.544800019 requires shifting the decimal point eight numbers to the left. Adding 8 to 28 increases the power of ten to 36, giving a normalized number of 1.544800019E+36.

If you want the cell to display 154800019E00028 , you will need to format the cell as text before putting in the 154800019E00028 .

I'm trying to convert numbers to text in Excel for e.g. convert "1" to "one", "2" to "two" etc.

Any suggestions would be greatly appreciated

Fatmah,

I could not find a Worksheet function to do this, but depending how many changes there are and how many different numbers need to be changed, you may be able to do this with a VLOOKUP.

In a test I ran I had numbers with values from 1-12. I created a 2 column range elsewhere on the spreadsheet. The 1st column had the 1.2.3 numbers and the 2nd column had their text equivelent. I inserted a new column to the right of where the original numbers were and entered the following to the right of the first value I wanted to change. The vlookup looked like this =VLOOKUP(A4,$F$4:$G$15,2,FALSE) Note: A4 was the cell that had the value I wanted changed. $F$4:$G$15 is the range of the lookup array. 2 is the column with the text value.

Needless to say, this would not be a good idea if every cell had a different number or there were thousands of possible numbers to be converted, but if you had thousands ofmnumbers in a limited range, this might be helpful.

<BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Fatmah:

I'm trying to convert numbers to text in Excel for e.g. convert "1" to "one", "2" to "two" etc.

Any suggestions would be greatly appreciated<HR></BLOCKQUOTE>

I am attempting to get Excel to create random numbers without repeating any already used. For example, in A1, I put in the argument "=RANDBETWEEN(1,4)" and 1,2,3, or 4 will appear in the cell. I repeat that for B1, C1, and D1. I don't know what to add to keep from getting a repeat number. Realistically, I should have any combination of 1, 2, 3, and 4 with each number appearing only once. I hope this makes sense.

I am trying to copy only the first 5 digits from one column into another. have seen many posts saying to use

=LEFT(b1,5) in column c

I have tried this and it returns nothing - i only see the formula in the fx box? what am i missing - have 16,000 lines to copy only 5 of 7 digits.

Help

If it returns nothing, check to make sure the column source is formatted as a strong, number, or text, and that it does not have leading blank characters.

Hi,

I have to print out an assignment I've done in Excel and Word, which includes both portrait and landscape pages.

The whole assignment will be bound in a portrait orientation, so all landscape pages will be sideways (i.e reading bottom to top).

The pages have to be numbered, which is easy enough to set up, although the landscape sheets (Excel) will print the page number at the foot of the sheet, which will appear as the right hand side when it is bound.

I need the numbers to print at the landscape sheets' left hand side (and side-on), so that when I bind them, each page has a page number appearing at the bottom of the sheet in the same place. Does this make sense?

I know I could print blank sheets with the page numbers at the foot in a portrait orientation and then put the paper back in the printer and print the landscape stuff without page numbers (!), but isn't there a way to make Excel do this for you?

Gram

I have a table / spreadsheet price list that's a full page... I want to double all of the numbers either by percentage or just double everything as a total.

For instance: 240.00 afterwards should be 480.00

I want to double everything at the same time.

The prices that need to double are in C through R, 8 through 52 and everything inbetween.

Is there a way to highlight the entire square table and just double the numbers? or off the page put the percentage of how much you want to increase the totals by? I've looked at templates but can't figure out how to apply the formula to what I'm doing.

When responding, please be very elementary in explaining on how step by step... I haven't worked in excel in many years and have had a stroke since.

Thank you,

Welcome to the board.

In a free cell, enter the number 2. Then copy that cell.

Select the cells you want to double, then Edit > Paste Special > Multiply > OK.

Question about formating numbers in Excel spreadsheet

I am exporting data from my HSBC bank account and it says it is

excell format. But it actually produces a comma deliminated column with about 20 rows

I have tried to use the "Text to Columns" tool under the Data tab and this works

but the 2 columns of numbers are in fact text so I can not do calculations on them.

I have uploaded the file it is data1.txt

Do you know how I can import this and convert those columns to numbers ?

Thanks.

If you change the name of the file to data1.csv (comma separated values) excel will open the file without going through the text to column wizard. To get the full functionality of excel you will need to 'Save As' and excel workbook. Once there you can select a column, right click, choose format, and pick a number format you like. The Excel workbook often will format for you if it detects numbers under the heading.

Hi,

For my expense reports I number the receipts according to the line numbers but when I print the sheets, the line numbers and column headings disappear. How can I print them?

Thanks for your help.

Cheers!

Tom

What line numbers ? do you mean Excels row numbers? You can print them by going to File/pagesetup, and on the Sheet tab check off Print Row and Column Headings.

If that is not what you mean then while in the Sheet tab still, is there a range set in the Print Area, that may be excluding your rows and columns?

(My first post)

I have a basic Excel spreadsheet that I use as an order form.

When I type a whole number into the quantity box and hit enter, it transforms into a two-place decimal number:

1 becomes .01

5 becomes .05

This happens no matter how many times I try to reformat the cell. It happens if I open Word and insert a spreadsheet into a document.

Something simple, or supernatural?

Thanks,

Rich

I need to put a number into cell A7, and the previous number that was in A7 rolls up to A6, and any number in the above cells to roll upwards also, with A1 rolling off of the page.

How would I do this?

I have some titles in an excel workbook like so.....

S1 D1

S10 D2

S10 D3

S10 D4

S9 D2

S9 D3

Click to expand...

The problem is the S10 titles should come after S9 titles. Is there a way to do this? If so, can it be done by macro?

One way would be replace s9 with s09

Your problem occurs from the character by character sorting of strings which are being interpreted as numerical.

I am using the formula's =IF(C7<2,0,IF(C7<5,3,IF(C7<7,6))) and =IF(c7<2 COUNTA(AA:AA),IF(C7<5 COUNTA(AD:AD),IF(C7<7 COUNTA(AG:AG)))) in a program this work ok if c7 relates to numbers, how can i change the formula if c7 relates to letters? is this possible to do

What do you mean exactly by relates to letters? Do you mean something like =IF(C7="A",...?

Something like =IF(ISTEXT(C7)=FALSE,1,2) should give you a start....

I have a very large workbook that has numbers in millions and thousands. I would like to be able to convert all numbers to thousands. In other words, the number 1,373,963 becomes 1,374 or the number 61,250 becomes 61.

Short of dividing hundreds of numbers by 1000, is there a way I can format to have this occur?

I'm not a Mac user, but a person I am working with is....

We have data exported from various applications into xls or csv files. When I open the files, I get the information needed. However, if the other person (using a mac) opens the file, the number field is changed to a date. The number input format is xxxx-xxxx & displays for her as x/x/xxxx. The date numbers are not at all similar to the orginal numbers (Example 1012-2012 does not display as 10/12/2012 but as 8/1/2514).

She is using excel for Mac 2011

Any suggestions?

Hi, welcome to the forum,

The problem is that the is too much 'intelligence' built in Excel.

Excel makes it's own intertrepation when enetring a number and if 'it thinks that it's a data' it set the format to date format.

When importing a csv file you can also set the the format general but I don't know how the MAc version does this.

You could Google something like "Excel maintain original fort when reading csv file" and see what that brings you.

You have to thank Miscrosoft for this 'built in intelligence'

Hiya All

I am building a database in MS Access that holds info on staff, clients and therapy sessions at a beauty parlour.

I have created a table in Access that holds each session and the cost of each sale.

I then went on to create a query with the between dates as the criteria.

I then created a report from this that worked and illustrated the sessions showing the therapy number, client number, staff number, treatments, cost, date and time.

I then found a button on the the toolbar which allowed me to analise this data in excel - working out the total earnings from the treatments. I arranged the titles, fields etc and added SUM calculations to work out total income.

unfortunately, every new report when transferred starts from scratch and i have to retype the formulae, move the field sizes etc, which is not acceptable.

Now, my question.

1. Is there a way to transfer the data into a template excel document so all remains same with new records being inputted, therfore allowing me to see the new total income?

or

2. Is there a macro command that could be put in a form that will input the data into a template document? - again allowing me to see total income without retyping the formulae everytime.

????

Hi everyone,

I've got a problem which I feel is going to be easy to solve, but I think I'm lacking that crucial nugget of knowledge to achieve it! I've tried a couple of similar things in VBA that I've picked up on the web, but I just cant get it to work.

What I'd like to do to the attached spreadsheet (sample attached) is where the number '1' is displayed in 'H20' for that to display a different number on each printed copy.

It needs to print numbers 1-22, then 29-42, then 51-54.

Ideally, I'd also like to add a 'Print' button to the spreadsheet so that just pressing that would automate the whole process, but one step at a time!

Thanks for your time!

JB

I am looking to compare columns of numbers. I have found related posts/answers but they don't do exactly what I am trying to do (I am a beginner at excel so find the complex formulas really confusing)

The data I have is 3 lists of numbers, I am trying to find out how many/which numbers are unique to each list, how many/which numbers are common between all 3 lists and how many/which numbers are common between 2 of the 3 lists.

My lists vary in length up to 1000 entries.

Up until now I have been changing the colour the text in each list, combining them and sorting - and then subtracting each number from the one above it to identify duplicates (and counting by hand) which is incredibly time consuming (as you can imagine)

I have attached an excel document with the three lists and what i have currently been doing.

Thank you in advance if you can help me!!!

looking for code to count order numbers but an order number may appear more than once if there are more than one material per order. In the report all orders and all the materials on the order are pulled in for a given time period. therefore if order 623456 has lines 10, 20, 30 then order 623456 appears on the report 3x. I cannot remove duplicates as the data that corresponds to these line items is needed for further info. I want to count the order numbers less the duplicates.

Thanks

Hi all,

I am running a few 'blank' invoices with different invoice numbers on each of them to send to clients. The number goes like this: 053/12 054/12 055/12 and want it to increase each time I print. At the moment, we do it manually and it takes forever and I am sure there has to be a macro that would do this for me but no idea how to do it. I'm a beginner with excel so really need some step by step help if possible.

The numbers are in cell J2 and in cell J49 (same number but on different sides of the invoice page). I obviously want the "055" number to go up but not the /12 as this represents the year.

Thanks for your help, it is much appreciated!

obsie

Greetings from Sydney, Australia.

In my last job we used Lotus and you could elect to show minus amounts with brackets around them. Now I have a new job and we use Excel 2000 that shows negative amounts with a minus sign, only. You can't change the format by using Format, Cells, Number.

How do I get negative amounts to show with brackets around them? Also, if this is possible, how do I nominate this feature as a default whenever I start a new Excel worksheet? For that matter, even if it isn't possible, how do I nominate any default options?

Thanks mate.

Hi Lee,

You can make the changes you want by using Format - Cells - Number.

Just choose Custom andyou will see a scroll down list of possibilities. None of these include bracketing so you just have to add them yourself in the TYPE field.

For instance, make

#,##0.00;[Red]-#,##0.00

read as

(#,##0.00);[Red](-#,##0.00)

to get the result

(-78.98)

in your selected fields.

We have a Revision page in our documents. I would like to know if there is a macro that would go thru the document and list all the page numbers that have track change bars on them. Documents could be around 400 pages long.

Is this possible?

Hello guys

I have a long report that need to insert numbers to pages to summarize the contents, but this need some intensive work to start the index with the roman numbers: I , II, III, V etc and after that will start with the normal naumbers, 1,2,3 etc..

any idea how to do that will be really highly appreciated,

many thanks,

From the Format menu, select Bullets and Numbering, click on the Numbered tab then on the style you want. Click on Customise and nominate the number at which you want to start then OK your way out.

I’d like to add page numbers to a word document but I don’t want the first and second page of the document to have page numbers.

The problem is: I don’t know how to make the second page free from numbers. Can anyone help me?

How to set up a document with front matter numbered separately

I don't really know what I did to cause the problem, but whatever it was I didn't intend to do it. I no longer have letters up for the column headers, this makes it a real pain to type in forumlas.

Anyway, attached is a screenie of what I'm talking about, anyone know how to make it normal again?

In Excel, click Tools/Options

Select the "General" tab and UNtick the "R1C1 Reference Style" checkbox.

the column headings have changed from letters to numbers. what happened?

This is probably fairly simple, but I'm just stuck. I would like to type in the total number of minutes and have it convert to total hours. For example, if I type in 70, I would like for it to convert to 1:10. Any ideas?

Thanks!

Good afternoon!

Hope someone here can help!

I produce a report in excel that has rolling information spanning back over a year and for some reason, between my saving the file with this months data and my boss opening it a day later, all cells containing numbers using general format have been changed to 00:00:00.

Cells that had already been set to a date format were not changed and the data is still available somewhere in the cell as all my graphs are still showing the correct info. Even all numbers on my Y axis are 00:00:00!!!

I have subsequently established today that my colleague is experiencing a similar issue, whereby all data in her reports is being changed to a date format of "dd mmm 1900" whenever it is saved.

In both instances, we have reformatted all cells back to general or number (whichever is suitable) and saved the file, only for it to revert back when we re-open.

I have attached an example which shows just one tab from each of the reports to show an example of what is happening - you will notice that any formulas and graphs are still calculating correctly, it is purely the view that keeps going wrong!!

Any help anyone can offer will be gratefully received.

Thanks and regards,

Chalkie247

you are getting the data from an external source - has this source changed its format at all

what is the source

do you update the data from external source each time you open the spreadsheet

who owns the external data /database you are using

When I enter a number like 2-2 it shows up as 2 Feb. What do I need to do to enter the numbers?

Thanks-

Hi all

I've been wrecking my brains on this Excel VBA probem. I have a column of number that I want to add up. These numbers vary something like this:

2

6

5

Total 13

1

9

10

5

1

Total 26 The sets are longer than this sample and are hundred in nature.

These continue on down the column. I have hundreds of these sets of numbers to add up and the sets vary in length. I have used the following code that someone gave to me.

Range("B1").Select

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Range("A1").Select

vRowTop = 1

vRowBottom = ActiveCell.Offset(-1, 0).Row

vDiff = vRowBottom - vRowTop + 1

Selection.FormulaR1C1 = "=SUM(R[" & -vDiff & "]C:R[-1]C)"

This code adds up all the sets of numbers from top to bottom and not individual sets. Is there a VBA code that can do this for me. Even if I put the curser on the first number of the set and run a macro to add them up would be very helpful. This would save some time instead of me doing a SUM formula for all of them.

Be very specific in your answers since I am very new at this and need all the guidance I can get.

Thanks for all your help in the past. You guys have been great.

Hi all

I want to add 000 (three zeros) before the numbers in a column of my excel worksheet.How can I do it?

thanks

Need help with Excel 2007.

I have a program that will put a long list of numbers into a Excel spreadsheet for me. What I need to do with these numbers is put them into number groups eg 4, 5 ,6 7, etc up to 12.

I would like excel to be able to take the numbers I have in the one big list and place them into the right column. Eg 4.65, 4.35, 4.87 would be placed in the column headed 4. And to also sort them numerically too.

Any help with this appreciated

I have a chart I need to import into excel to adjust the numbers. It looks like it was built by excel early on and graphics of the company name and other things written on it. How I import it from a neat receipts scanner into excel so I can change the numbers. I can upload the image if needed. I really don't know what to ask so if you could post some questions it would make my questions more exact. Thanks for the help.

Generally, a chart is created by the data in the spreadsheet, so I think that what you want to do is to update the spreadsheet, which will update the chart.

Now if the chart is something different---say from a Powerpoint presentation, you can certainly import it, but you will not be able to change it.

It would help if you can tell us precisely what file type the chart is, and where you found it. What kind of changes do you want to make in the chart?

Regards,

John

My problem is with Excel 2002 and negative numbers. This version of excel does not have brackets for negative numbers in the "format" "cells" section of Excel. I have tried changing the currancy values in the regional settings, but with no luck. Has anyone seen this problem before? Some versions of XP do have it, while others don't. It is not restricting itself to either small business, professional or standard (XP) either. I have also applied all of the updates and service packs to office.

I have looked and saw that i can do a custom format, however, does this formatting apply itself to all sheets or just the sheet, or cells that you are working on?

I really wanted to apply this format to all sheets.

Thanks

Can you describe the format? Did you try creating a custom format.

Hi there...I'm new to VBA and I don't know if what I need to do can actually be done.

We have forms that we put out on our companies web site that employees can down load and use. One of which is a travel expense reimbursement form. Accounts Payable uses the Trip Number when reimbursing the expense.

I need Excel to create a unique number every time the form is downloaded from the website. Maybe I'm not understanding the posts I've read but it seems that the Excel file needs to be saved on one computer/used by one individual to do this.

Is there a way to create a new travel number each time the file is downloaded and opened or does Excel need the user to save the file so that it knows which number to start with the next time?

The number I'm looking to create would be T100000, T100001, T100002 etc. It would need to be entered in cell K5. The file also needs to be an Excel 97-2003 document to be loaded to our site.

Thanks in advance!

Ali

Hi,

I have some reference books I use and in all of them I have this problem:

The number written on the document pages doesn't match the number my pdf reader says, and for every document it has a different offset. That is, for example when I go to page 16, then on the page itself it's written that it's page 5 and not 16.

It's an annoying problem.

Is there any way to fix this by changing the pdf numbering or is there any workaround?

Thanks

I would appreciate help with an excel formula. I am not a techie so I would appreciate if responses are as transparent as possible. Column J lists participant start dates and Column K lists participant end dates. There are a myriad of start and end dates and many participants so the sort function does not suffice. I am hoping that it is possible to write a formula in which I can check total participants onsite on any given Monday (or week) of the year. Any help would be great. Thanks very much.

Excel'03?

How do I add a number in front on a colum of numbers in one move.

I have a bunch of item numbers on a price list, about 1000.

I need to a the number 7 in front of all of them!

anyone know how to do this!

please help!!

Brion

RE: Excel 2007

Worksheet page numbers are no longer at the bottom of the page.

What can I do to view them?

Help!!!

Jazdup

Hi there, welcome to the board!

What do you mean "Worksheet page numbers"? Can you describe for us what exactly which you are looking at and what view type you are in?

Excel 2010

I have a column of 22,000 phone numbers (and an additional column of fax numbers).

I'm trying to convert the numbers from nine digits to nine digits with hyphens.

I copied and renamed the original document. There are no protections.

To be certain, I ran the Doc Inspector. I'm still free to do anything to the list.

The instructions are easy:

Highlight column, select Format cell, click Custom, in Type, insert XXX-XXX-XXXX, click OK. That didn't work.

Next, instead of click Custom, I clicked Special, selected phone number, clicked OK.

Instead of hyphens, if it adds parentheses around the area code. I'm not that picky, although it takes more space.

That doesn't work either.

I am less than a novice in Excel when it comes to creating anything. I've managed to fill in others and not have to create my own.

The only thing that doesn't look like instructions is that the Sample above Type never changes.

I don't know if it should.

Thank you.

I have a column that contains the following values:

>6Hrs but <24 Hrs

1 Day to 7 Days

8 to 21 Days

> 21 Days

Not my choice (or my data), however:

=COUNTIF(Complete!$V$2:$V$314,">6 Hrs but <24 Hrs")

=COUNTIF(Complete!$V$2:$V$314,"8 to 21 Days*")

=COUNTIF(Complete!$V$2:$V$314,">21 Days")

All produce the same count (which is obviously wrong or why would I ask). I suspect that COUNTIF is considering the numbers in the criteria to be numeric values rather than text.

I've also tried

=COUNTIF(Complete!$V$2:$V$314,T(">21 Days"))

Searching through the Excel help files I don't see a way to quote these numbers or force COUNTIF to treat them as text.

Any ideas? Thanks -ranz

OBTW: I mistakenly started this discussion in "Excel 2003 COUNTIF function help" - mea culpa