## How To Add A Letter In Front Of Each Number In A Cell |

## How To Add A Letter In Front Of Each Number In A Cell - Excel |
View Answers |

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

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

## Free Excel Help Forum

**- Ask any question about Excel and have it answered in no time.**

## Similar Excel Tutorials

Convert Column Number to Letter Using a Formula in Excel

How to get a column letter from a number in Excel using a simple formula. This is an important thing to be able to ...

How to get a column letter from a number in Excel using a simple formula. This is an important thing to be able to ...

Capitalize First Letter of Every Word in a Cell - PROPER Function

In Excel you can use a function to capitalize the first letter of every word in a cell. This allows you to prepare ...

In Excel you can use a function to capitalize the first letter of every word in a cell. This allows you to prepare ...

Select Cells in Excel using Macros and VBA

This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...

This is actually a very easy thing to do and only requires a couple lines of code. Below I will show you how to sel ...

Wildcards in Excel

Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wil ...

Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wil ...

## Subscribe for Weekly Tutorials

### Helpful tutorials delivered to your email!

## Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File

- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Replace Formulas with Values (For The Entire Workbook)

- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Save the Current Worksheet as a New Excel Workbook File

- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Format Cells in The Number (Numerical) Number Format in Excel

- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel

- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel

Macro to add a New Line to Message Box Pop-up Windows in Excel

- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

## Similar Topics

We have a number of Excel users in our office who cannot copy and paste

between Excel workbooks. They can copy and paste between worksheets. When

you highlight the section to copy and then go to the new workbook both the

paste

and paste special are "grayed out". This is true whether you right-click the

mouse, go to the edit menu, or use control keys. This occurs with any data

type and the most simple workbooks. I have seen some suggestions here but

none have worked for this particular problem. I have reset the menus and

renamed the .xlb files and neither helps. You can open the clipboard and the

paste will work, but there is no paste special option. Any help would be

greatly appreciated. Thanks!

between Excel workbooks. They can copy and paste between worksheets. When

you highlight the section to copy and then go to the new workbook both the

paste

and paste special are "grayed out". This is true whether you right-click the

mouse, go to the edit menu, or use control keys. This occurs with any data

type and the most simple workbooks. I have seen some suggestions here but

none have worked for this particular problem. I have reset the menus and

renamed the .xlb files and neither helps. You can open the clipboard and the

paste will work, but there is no paste special option. Any help would be

greatly appreciated. Thanks!

Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.

James

James

Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,

Randy

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,

Randy

I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:

A1: 50

A2: 10

B1: 60

B2: 20

A3: Formula: =A1+A2 Displays: 60

Right click A3, Copy, right click B3, paste

A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?

Example:

A1: 50

A2: 10

B1: 60

B2: 20

A3: Formula: =A1+A2 Displays: 60

Right click A3, Copy, right click B3, paste

A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?

Dear Sirs,

Am in need for this solution very badly and what could be a better place than excelforum !

I have an MS Excel File (2007 version) sample file attached, which has name, designation, blood group and so on. The last column is for hyperlinking photographs of individuals.

In the same folder where I have saved this excel file, are lying photographs of individuals. While scanning the photographs, I have saved them serially i.e. 1,2,3 and so on.

In the Excel file, in last column, I have given the respective serial numbers. In order to hyperlink one has to select that particular Cell, press Ctrl K and you automatically go to the folder containing individual photographs, you select that photo and OK.

Problem :

I have to do this hyperlinking one by one and if there 1000 photos, lot of time is wasted.

Solution Needed :

Just in case of excel formula, which we copy and paste, Can I get a command by virtue of which the column titled Photo or column next to it gets automatically Hyperlinked to respective photo WHEN I copy and paste such command to all cells in that column.

Thanks a million and warm regards ::: Jack

Am in need for this solution very badly and what could be a better place than excelforum !

I have an MS Excel File (2007 version) sample file attached, which has name, designation, blood group and so on. The last column is for hyperlinking photographs of individuals.

In the same folder where I have saved this excel file, are lying photographs of individuals. While scanning the photographs, I have saved them serially i.e. 1,2,3 and so on.

In the Excel file, in last column, I have given the respective serial numbers. In order to hyperlink one has to select that particular Cell, press Ctrl K and you automatically go to the folder containing individual photographs, you select that photo and OK.

Problem :

I have to do this hyperlinking one by one and if there 1000 photos, lot of time is wasted.

Solution Needed :

Just in case of excel formula, which we copy and paste, Can I get a command by virtue of which the column titled Photo or column next to it gets automatically Hyperlinked to respective photo WHEN I copy and paste such command to all cells in that column.

Thanks a million and warm regards ::: Jack

After entering a text in a cell or in the function field, when i go back in

the cell an apostrophe apear in front of the text.

How can i correct this?

Thank you

the cell an apostrophe apear in front of the text.

How can i correct this?

Thank you

I was wondering if anyone could help me out please?!?!

I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??

Example:

Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc... Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1.

I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!! Thanks in advance...

P.S. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers...

I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps

-Aric

I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??

Example:

Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc... Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1.

I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!! Thanks in advance...

P.S. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers...

I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps

-Aric

Posted this on the Ozgrid forums, but haven't gotten any help yet, so I thought I'd try here too!

I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.

I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?

______________________

Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.

If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...

Any help would be much appreciated. This is driving me nuts!

I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.

I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?

______________________

Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.

If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...

Any help would be much appreciated. This is driving me nuts!

I would like to copy a small table from Word into one cell in an Excel

worksheet. The first column of the table is a list of numbers. I tried

converting the table into text with manual line breaks and tab stops to

divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I

get a warning that the selection contains multiple data values, and

merging into one cell keeps the upper-left most data only.

What I tried that didn't work:

* Formatting the Excel cells as text before pasting the data.

* The various options for "Paste Special." The closest I got was

inserting the table as a Document Object, which could be a workaround,

I guess.

What I am saving for when all else fails:

* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I

would like to have reference charts of how to identify the strength of

each tablet by its color and markings. I got the info from the

manufacturers' websites and entered it into tables in Word, which I

would like to copy into a more comprehensive file I am creating in

Excel. The first column of each table is the strength of the tablet,

entered as 1 mg., 2 mg., etc. The subsequent columns describe the

shape, color, and markings. There are 3 tables, each with about 4-5

rows.

Is there a way to copy each one - whether as a table or as text - into

a single Excel cell without losing data?

Many thanks.

worksheet. The first column of the table is a list of numbers. I tried

converting the table into text with manual line breaks and tab stops to

divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I

get a warning that the selection contains multiple data values, and

merging into one cell keeps the upper-left most data only.

What I tried that didn't work:

* Formatting the Excel cells as text before pasting the data.

* The various options for "Paste Special." The closest I got was

inserting the table as a Document Object, which could be a workaround,

I guess.

What I am saving for when all else fails:

* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I

would like to have reference charts of how to identify the strength of

each tablet by its color and markings. I got the info from the

manufacturers' websites and entered it into tables in Word, which I

would like to copy into a more comprehensive file I am creating in

Excel. The first column of each table is the strength of the tablet,

entered as 1 mg., 2 mg., etc. The subsequent columns describe the

shape, color, and markings. There are 3 tables, each with about 4-5

rows.

Is there a way to copy each one - whether as a table or as text - into

a single Excel cell without losing data?

Many thanks.

I am trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as:

A1 type in 10, A2 type in =A1 (calculated A2 to be 10)

B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.

All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?

A1 type in 10, A2 type in =A1 (calculated A2 to be 10)

B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.

All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?

I did a search in here but didn't find an answer so here goes nothing...

I have a spreadsheet with data from A1 to H1 down to A275 to H275. I select all of it, Copy, go into the Sheet2 tab and select A1, Paste Special -> Transpose and it give me the following error message:

Quote:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:

- Click a single cell, and then paste.

- Select a rectangle that's the same size and shape, and then paste.

For the life of me, I can't figure out why it's not working! I have done this many times with other spreadsheets. Has anyone ever experienced this? If so, were you able to resolve it and how?

Many Thanks,

Mike

I have a spreadsheet with data from A1 to H1 down to A275 to H275. I select all of it, Copy, go into the Sheet2 tab and select A1, Paste Special -> Transpose and it give me the following error message:

Quote:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:

- Click a single cell, and then paste.

- Select a rectangle that's the same size and shape, and then paste.

For the life of me, I can't figure out why it's not working! I have done this many times with other spreadsheets. Has anyone ever experienced this? If so, were you able to resolve it and how?

Many Thanks,

Mike

Hi guys,

would really appreciate anyones help with this.

I have a column full of text-formatted fractions...

4/5

6/4

3/1

2/5

4/5

etc, etc.....the column is very long!!

I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.

If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!

Any suggestions on how I could speed this up?

thanks

would really appreciate anyones help with this.

I have a column full of text-formatted fractions...

4/5

6/4

3/1

2/5

4/5

etc, etc.....the column is very long!!

I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.

If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!

Any suggestions on how I could speed this up?

thanks

Is it possible to have numbers added to the same cell and have excel continue

to calculate the addition for me in that same cell......ex: I have the number

8 in cell d2 and I want to add the number 8 to that cell and have excel add

the 8 to the previous 8 for a total of 16 in the same cell.....the next time

I would add 5, and the total would be 21? Can this be done in a single cell?

to calculate the addition for me in that same cell......ex: I have the number

8 in cell d2 and I want to add the number 8 to that cell and have excel add

the 8 to the previous 8 for a total of 16 in the same cell.....the next time

I would add 5, and the total would be 21? Can this be done in a single cell?

Hi,

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1

2

3

4

Fig. 2

1 1

2

3

4 4

Fig. 3

1 1

2 4

3

4

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1

2

3

4

Fig. 2

1 1

2

3

4 4

Fig. 3

1 1

2 4

3

4

Right now I have a sports league with 8 teams, I'd like to create a random 7-game schedule where each team plays the other team only once. Essentially this will be a 7-game round robin. However, I'd like to be able to use this for any number of teams and games.

I'd like to do this in Excel, but I can't figure out how to have a randomly generating non-repeating macro with text values in cells.

So right now I have a column of 8 values and need matrix of 7 columns by 8 rows next to it.

I've found this thread that has one for numbers, but I can't figure out how to do it for text values...

http://www.excelforum.com/excel-prog...m-numbers.html

Thanks all!

I'd like to do this in Excel, but I can't figure out how to have a randomly generating non-repeating macro with text values in cells.

So right now I have a column of 8 values and need matrix of 7 columns by 8 rows next to it.

I've found this thread that has one for numbers, but I can't figure out how to do it for text values...

http://www.excelforum.com/excel-prog...m-numbers.html

Thanks all!

Hi All,

I am trying to make excel automatically add a leading zero to values which are 5 digits long;

i.e. number input is 15185, then excel automatically changes it to 015185.

If I put a Customer Number Format of 0##### it works, however, a user could put any length of number into these cells, and if the number is less than 5 digits I don't want a leading zero.

Is there any way of writing a small macro to sort this out.

The numbers would be input into range B16:223.

Many thanks,

Andy

I am trying to make excel automatically add a leading zero to values which are 5 digits long;

i.e. number input is 15185, then excel automatically changes it to 015185.

If I put a Customer Number Format of 0##### it works, however, a user could put any length of number into these cells, and if the number is less than 5 digits I don't want a leading zero.

Is there any way of writing a small macro to sort this out.

The numbers would be input into range B16:223.

Many thanks,

Andy

I am trying to figure out how to write a formula to figure out production cycle time.

We will be building 8 "widgets" a day.

We will work 10 hours a day.

There is a 20 minute break at 9:20 am.

Production shuts down for lunch 30 minutes for lunch at 12:30.

Production starts at 6:00 am.

Here is what I can do.

Production cycle time = (10*60)-30/8 or 71.25 minutes

If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.

This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.

=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))

I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.

I have bought a number of books in an attempt to figure this out, and I am still stumped

We will be building 8 "widgets" a day.

We will work 10 hours a day.

There is a 20 minute break at 9:20 am.

Production shuts down for lunch 30 minutes for lunch at 12:30.

Production starts at 6:00 am.

Here is what I can do.

Production cycle time = (10*60)-30/8 or 71.25 minutes

If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.

This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.

=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))

I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.

I have bought a number of books in an attempt to figure this out, and I am still stumped

Hello, I'm trying to count the number of times a name appears across multiple worksheets but I can't seem to figure it out.

I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.

On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.

For example Sheet 7 (called total) would have

Joe 5 (where 5 is the outcome of the formula I'm looking for to count all the times "Joe" appears on sheets 1-6).

I have it counting per sheet with =COUNTIF(A1:A130,"name") but this is not quite what I am looking for.

Thanks in advance for any advice.

Edit: I am using Excel 2007 w/ windows XP

I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.

On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.

For example Sheet 7 (called total) would have

Joe 5 (where 5 is the outcome of the formula I'm looking for to count all the times "Joe" appears on sheets 1-6).

I have it counting per sheet with =COUNTIF(A1:A130,"name") but this is not quite what I am looking for.

Thanks in advance for any advice.

Edit: I am using Excel 2007 w/ windows XP

Hello,

I'm a bit of a newbie with Excel, but...

I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.

If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.

Thanks.

I'm a bit of a newbie with Excel, but...

I have several cells with data that contain both letters and numbers (4H, 8V, 4FH, etc.) What I want to do is remove only the text characters from these cells, and add the remaining number values together among a series of cells with this data type.

If your answer involves using a macro or VB, please provide a link on how to use the formula. I've never used a macro or VB.

Thanks.

I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks

Hello ,

Here is my issue:

I have a list of SKUs in Excel and I need to add one same word to 500 cells that have pre-existing text in the cells. See example below:

1) List of SKU's in cells:

34

35

39

55

2) text that needs to be added IN FRONT of every number:

DF

So the result would be:

DF34

DF35

DF39

etc.

How do I do that in a formula and not manually? I found another similar thread and I understand how to do it from the instructions there but the same text goes on the back and the result is: 34DF but I need it to be like: DF34.

I am using Office 2007 for mac.

Thank you for the help,

Chris

Here is my issue:

I have a list of SKUs in Excel and I need to add one same word to 500 cells that have pre-existing text in the cells. See example below:

1) List of SKU's in cells:

34

35

39

55

2) text that needs to be added IN FRONT of every number:

DF

So the result would be:

DF34

DF35

DF39

etc.

How do I do that in a formula and not manually? I found another similar thread and I understand how to do it from the instructions there but the same text goes on the back and the result is: 34DF but I need it to be like: DF34.

I am using Office 2007 for mac.

Thank you for the help,

Chris

i've been trying to figure this out on my own but seem to be hitting road blocks.

i have a column of numbers that we extracted from a database as whole numbers. for instance, 30 should be .30 and 100 should be 1.00. when i try to increase or decrease the decimal point it doesn't allow me to convert it to those positions.

would any one have a tip of how i can easily convert this? there are over 2000 entries in my excel spreadsheet so to do it manually would take a lot of time/effort.

thank you!

i have a column of numbers that we extracted from a database as whole numbers. for instance, 30 should be .30 and 100 should be 1.00. when i try to increase or decrease the decimal point it doesn't allow me to convert it to those positions.

would any one have a tip of how i can easily convert this? there are over 2000 entries in my excel spreadsheet so to do it manually would take a lot of time/effort.

thank you!

Hello from sunny Madrid

I have created a complex formula to help me calculating Golf handicaps for players.

The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.

But ALL the values change and I want some to change and some to stay constant.

How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.

Many thanks for your help

Ole

I have created a complex formula to help me calculating Golf handicaps for players.

The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.

But ALL the values change and I want some to change and some to stay constant.

How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.

Many thanks for your help

Ole

Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value.

But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.

This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.

Thanks for any tips on this.

But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.

This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.

Thanks for any tips on this.

I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook.

Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff.

Each worksheet will be identical, using columns A-I with row 1 having the headings:

Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell).

There will be a varying number of rows in each of the individual sheets.

If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.

If anyone can help it would really cut down the time I spend collating these stats every day!

Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff.

Each worksheet will be identical, using columns A-I with row 1 having the headings:

Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell).

There will be a varying number of rows in each of the individual sheets.

If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.

If anyone can help it would really cut down the time I spend collating these stats every day!