custom function to convert numbers stored as text
Hi everyone, I know you can convert text to numbers with the whole paste multiply by 1 thing but I was wondering if anyone had seen code (or would know what code to use) to convert numbers stored as text to numbers using a custom built VBA function? Any help greatly appreciated! Josh
|
0 |
|
Utf
1/28/2010 12:40:01 AM
excel.programming
6508 articles. 
2 followers.
4 Replies
1607 Views
Not sure what you mean, but here is a function that will convert a string value digit to an integer value. Function MakeNum(rng As Range) MakeNum = CInt(rng) End Function To use it, assume a string value of "123456" in cell b5: In an empty cell put formula =MakeNum(b5) > Enter. "Josh Craig" <JoshCraig@discussions.microsoft.com> wrote in message news:D788CF8C-7371-4B01-99AC-A433E84DE4C5@microsoft.com... > Hi everyone, > > I know you can convert text to numbers with the whole paste multiply by 1 > thing but I was wondering if anyone had seen code (or would know what code > to > use) to convert numbers stored as text to numbers using a custom built VBA > function? > > Any help greatly appreciated! > > Josh
|
|
0 |
|
JLGWhiz
1/28/2010 4:09:39 AM
There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob "Josh Craig" wrote: > Hi everyone, > > I know you can convert text to numbers with the whole paste multiply by 1 > thing but I was wondering if anyone had seen code (or would know what code to > use) to convert numbers stored as text to numbers using a custom built VBA > function? > > Any help greatly appreciated! > > Josh
|
|
0 |
|
Utf
1/28/2010 4:17:01 AM
Josh, There is no way of knowing what you truly mean because you didn't provide an example (as was mentioned in a prior post). However, if you have a numeric value in the spreadsheet and the spreadsheet is treating that numeric value as text, then here are two built-in Excel ways that should get that text into a number. (1) In a separate cell: =NumberTextCell+1-1. Copy (new formula cells)-Paste Special-Values (2) Place a 1 somewhere on the spreadsheet. Copy the 1-select numeric cells that are text-Paste Special-Multiply Best, Matthew Herbert "Josh Craig" wrote: > Hi everyone, > > I know you can convert text to numbers with the whole paste multiply by 1 > thing but I was wondering if anyone had seen code (or would know what code to > use) to convert numbers stored as text to numbers using a custom built VBA > function? > > Any help greatly appreciated! > > Josh
|
|
0 |
|
Utf
1/28/2010 4:57:01 AM
OR do you mean code to conver to numbers Dim cell As Range For Each cell In Selection If cell.Text <> "" And IsNumeric(cell.Value) Then cell.Value = Val(cell.Text) cell.NumberFormat = "General" End If Next -- Jacob "Jacob Skaria" wrote: > There is no direct functions to convert this. For a VBA solution check out > the below links > > http://support.microsoft.com/kb/213360 > http://www.ozgrid.com/VBA/ValueToWords.htm > http://www.xldynamic.com/source/xld.xlFAQ0004.html > > -- > Jacob > > > "Josh Craig" wrote: > > > Hi everyone, > > > > I know you can convert text to numbers with the whole paste multiply by 1 > > thing but I was wondering if anyone had seen code (or would know what code to > > use) to convert numbers stored as text to numbers using a custom built VBA > > function? > > > > Any help greatly appreciated! > > > > Josh
|
|
0 |
|
Utf
1/28/2010 5:23:01 AM
Similar Artilces:
Excel Text Function
Hi anyone who can help me... I have some info in a spreadsheet as follows: A1 B1 C1 Centra Dublin Centra Belfast Centra London If I want to get just Centra out into another cell I would use =LEFT(A1:C1,6) and this works fine. But I want to actually get out the area - Dublin, Belfast or London and some other areas that might have more or less than 7 letters. Any ideas??? Thanks in advance Ann (Dublin, Ireland) =TRIM(SUBSTITUTE(A1,"Centra","")) will work if you have city names and centra.. -- Regards, Peo Sjoblom "Ann&q...
Sequentially numbered Purchase orders
In the past with Office 97. I used a purchase order template that would basically self number itself. when it opened a small box would appear and by clicking it the PO received a unique number. How can I accomplish this using Office XP. ...
Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...
How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...
Convert single colum/multiple rows to multiple colums.
Hi, I have a .dat file when opened with Excel it has 1 column and 7 rows per entry. I would like to delete some rows and convert the rest to something like. Any chance this can be done. It's quite large. 51,793 rows. TIA Jeff Col 1 Col 2 Col 3 Name Date Lenny Kravitz - 2000 - Greatest Hits -- Table: {2} { "music" "Name", "06/04/2008", "Lenny Kravitz - 2000 - Greatest Hits", }, --Table: {3} etc. etc. ...
Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...
How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...
Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...
Article published by Microsoft reg. 'Event' custom entity
Recently, I found a great article published by Microsoft that contains a sample code on how to create a custom entity, event. I thought that I bookmarked it but cannot find it. Has anyone seen it and can provide a hyperlink? I will really appreciate it. http://msdn2.microsoft.com/en-us/library/aa682866.aspx you'll probably find it in the above link "mkatsev" wrote: > Recently, I found a great article published by Microsoft that contains a > sample code on how to create a custom entity, event. I thought that I > bookmarked it but cannot find it. Has anyone seen...
VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...
how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...
Batch converting leads to accounts
Is there a way to convert leads to accounts in batches? I need to convert up to 500 each day and don't have the time to go through them one at a time. Dave Lagergren On Sat, 26 Feb 2005 01:28:44 GMT, "Who Cares!" <whocares@freetidet.org> wrote: >Is there a way to convert leads to accounts in batches? I need to >convert up to 500 each day and don't have the time to go through them >one at a time. Only way is through the SDK. Write some code that loops through your leads and creates accounts, contacts and opportunities as needed and marks the lead as qu...
Mounting exchange 2000 public folder store on exchange 2003
I have been told that this is not best practice we have gone ahead and done this we where having a great deal of difficulty getting the public folders to replicate. The public folder mail box was incorrectly named I used ADSIedit to change the mail box name in AD but this didn't work. So in the end we went for a dirty fix (users had lost access to public folders for one day already) and dismounted then copied the exchange 2000 public databases to the 2003 box and mounted them there with apparent success. I'm wondering if anyone has any experience with this? Can I expect this s...
Adding up negative numbers only
Ok, let me try to explain this problem. I have several cells a1 throug a10. Each cell has a number, for ex. $10.00, ($5.00),etc. , sometime this number is positive, sometimes it is negative. I want to only ad all of the negative numbers in cells a1:a10 and put a total in cel a11. How can I do this? Thanks Ton -- tonydep ----------------------------------------------------------------------- tonydepo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1530 View this thread: http://www.excelforum.com/showthread.php?threadid=26928 =SUMIF(A1:A10,"<0&quo...
Sequential Numbering
I would like to know the formula for making a cell be able to do sequential numbering such as for purchase orders. Tom, A simple way would be to use something like this, you could assign it to a button, an open or before print event. Sheets("Sheet1").Range("A1").Value = _ Sheets("Sheet1").Range("A1").Value + 1 For other ways to do this or if this is going to be used in a temple have a look here http://www.mcgimpsey.com/excel/udfs/sequentialnums.html And if you are new to macros you may also what to have a look here on getting start...
Automatic Number Sequence
I would like to make a form in excel that automatically generates a number of the form in one of the cells. How can I go about doing this, that everytime I print a sheet it automatically generates a new number in the sequence? I have tried playing around with macros and it doesn't seem to change, only stay at a specific number. Is there an easier formula that this can be done with or do I have to use macro's and how do I do it with macro's if that's what's required?? It's a product information form for new products that need entering into the system. Hi Awissa, ...
Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that pa...
Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...
Help replacing text with Yes or No
I have a field formated as general. The field contains either 1 or is left blank. If the field has a 1 I want to replace it with Yes and if the field is blank I want to replace it with No. any help is appreciated. -- Jerry Save your data and use a copy for this exercize........... Assuming your data in Column A, put this in B1 and copy down........ =IF(A1=1,"Yes","No") Then highlight the column and do Copy > PasteSpecial > Values to get rid of the formulas..........then delete column A if you wish....... Vaya con Dios, Chuck, CABGx3 "Jerry Arnone, ...
Which Function...
will allow me to calculate a value on a form depending on the information in two other fields? For instance: If ad_Advertiser can have 3 values Private party, Commercial or National and each has a different rate and the cost of an ad is dependent on the number of words in the ad, i would expect to write something like this: CCUR(IIF([ad_Advertiser]=Private party, 3.50+(([wordcount]-10)*.35); IIF([ad_Advertiser]=Commercial, 4.50+(([wordcount]-10)*.45); IIF([ad_Advertiser]=National, 6.50+(([wordcount]-10)*.65) I know that the IIF function is not correct but not sure what to use in its place...
Inserting dashes to an existing number
Hi, I am trying to create a formula that will divide this ten digit numbe into the following dashes. Here is the number 1234000999 I need the dashes to be inserted in thi format 1234-000-999. What is the appropriate formula. Thanks for your help. Dinahros -- dinahros ----------------------------------------------------------------------- dinahrose's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1334 View this thread: http://www.excelforum.com/showthread.php?threadid=26643 Why not just custom format right click on selection>format>number>custom...
Can't Convert Quicken 2001 Deluxe Data to MS Money 2004
First I tried to convert the Quicken Data using the MS Money 2003 OEM Version that came with the new computer I bought last week. Then I purchased MS Money 2004 Standard Edition and still couldn't convert my Quicken data. On the box, Microsoft says they offer a 30 day money back guarantee with no details about how to get your money back. Does anyone know how I can get my money back? Regards, John E. Golden ...
Number Rows on a report?
It would be anice addition if I could add numbers (a count) in front of each row on my filtered report. Any easy way to do this. I was thinking an unbound text box with something in the control source - I just don't know the something :) Any help here will be appreciated. Thanks in advance On Wed, 20 Jun 2007 14:07:27 -0700, Dave wrote: > It would be anice addition if I could add numbers (a count) in front of each > row on my filtered report. > > Any easy way to do this. > > I was thinking an unbound text box with something in the control source - I > just...
How do I convert csv to columns?
Can someone tell me (using Excel 2330) how I make a csv file which has semi colon separation points into columns? thanks After you open this file all of your data should be in column A, select this column and Data, Text to Columns, Delimited, by semicolon should give you the result you seek. plato Wrote: > Can someone tell me (using Excel 2330) how I make a csv file which has > semi > colon separation points into columns? > > thanks -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelfo...
XML File Converter for MS 2004
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: intel I'm a high school teacher. I write power points on MS 2004 on my Mac and transfer them to my school's PC that has MS 2003. This worked great, with exception of minor formatting changes, until a month ago. Power points that I previously opened on that PC, along with new ones, would not open - this is disasterous for instruction. My tech says 1) the school's PC may have had updates that caused the new problem and 2) that I should install the Open XML File Converter for Mac's MS 2004. I'm concerned tha...
Hi anyone who can help me... I have some info in a spreadsheet as follows: A1 B1 C1 Centra Dublin Centra Belfast Centra London If I want to get just Centra out into another cell I would use =LEFT(A1:C1,6) and this works fine. But I want to actually get out the area - Dublin, Belfast or London and some other areas that might have more or less than 7 letters. Any ideas??? Thanks in advance Ann (Dublin, Ireland) =TRIM(SUBSTITUTE(A1,"Centra","")) will work if you have city names and centra.. -- Regards, Peo Sjoblom "Ann&q...
Sequentially numbered Purchase orders
In the past with Office 97. I used a purchase order template that would basically self number itself. when it opened a small box would appear and by clicking it the PO received a unique number. How can I accomplish this using Office XP. ...
Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...
How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...
Convert single colum/multiple rows to multiple colums.
Hi, I have a .dat file when opened with Excel it has 1 column and 7 rows per entry. I would like to delete some rows and convert the rest to something like. Any chance this can be done. It's quite large. 51,793 rows. TIA Jeff Col 1 Col 2 Col 3 Name Date Lenny Kravitz - 2000 - Greatest Hits -- Table: {2} { "music" "Name", "06/04/2008", "Lenny Kravitz - 2000 - Greatest Hits", }, --Table: {3} etc. etc. ...
Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...
How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...
Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...
Article published by Microsoft reg. 'Event' custom entity
Recently, I found a great article published by Microsoft that contains a sample code on how to create a custom entity, event. I thought that I bookmarked it but cannot find it. Has anyone seen it and can provide a hyperlink? I will really appreciate it. http://msdn2.microsoft.com/en-us/library/aa682866.aspx you'll probably find it in the above link "mkatsev" wrote: > Recently, I found a great article published by Microsoft that contains a > sample code on how to create a custom entity, event. I thought that I > bookmarked it but cannot find it. Has anyone seen...
VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...
how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...
Batch converting leads to accounts
Is there a way to convert leads to accounts in batches? I need to convert up to 500 each day and don't have the time to go through them one at a time. Dave Lagergren On Sat, 26 Feb 2005 01:28:44 GMT, "Who Cares!" <whocares@freetidet.org> wrote: >Is there a way to convert leads to accounts in batches? I need to >convert up to 500 each day and don't have the time to go through them >one at a time. Only way is through the SDK. Write some code that loops through your leads and creates accounts, contacts and opportunities as needed and marks the lead as qu...
Mounting exchange 2000 public folder store on exchange 2003
I have been told that this is not best practice we have gone ahead and done this we where having a great deal of difficulty getting the public folders to replicate. The public folder mail box was incorrectly named I used ADSIedit to change the mail box name in AD but this didn't work. So in the end we went for a dirty fix (users had lost access to public folders for one day already) and dismounted then copied the exchange 2000 public databases to the 2003 box and mounted them there with apparent success. I'm wondering if anyone has any experience with this? Can I expect this s...
Adding up negative numbers only
Ok, let me try to explain this problem. I have several cells a1 throug a10. Each cell has a number, for ex. $10.00, ($5.00),etc. , sometime this number is positive, sometimes it is negative. I want to only ad all of the negative numbers in cells a1:a10 and put a total in cel a11. How can I do this? Thanks Ton -- tonydep ----------------------------------------------------------------------- tonydepo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1530 View this thread: http://www.excelforum.com/showthread.php?threadid=26928 =SUMIF(A1:A10,"<0&quo...
Sequential Numbering
I would like to know the formula for making a cell be able to do sequential numbering such as for purchase orders. Tom, A simple way would be to use something like this, you could assign it to a button, an open or before print event. Sheets("Sheet1").Range("A1").Value = _ Sheets("Sheet1").Range("A1").Value + 1 For other ways to do this or if this is going to be used in a temple have a look here http://www.mcgimpsey.com/excel/udfs/sequentialnums.html And if you are new to macros you may also what to have a look here on getting start...
Automatic Number Sequence
I would like to make a form in excel that automatically generates a number of the form in one of the cells. How can I go about doing this, that everytime I print a sheet it automatically generates a new number in the sequence? I have tried playing around with macros and it doesn't seem to change, only stay at a specific number. Is there an easier formula that this can be done with or do I have to use macro's and how do I do it with macro's if that's what's required?? It's a product information form for new products that need entering into the system. Hi Awissa, ...
Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that pa...
Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...
Help replacing text with Yes or No
I have a field formated as general. The field contains either 1 or is left blank. If the field has a 1 I want to replace it with Yes and if the field is blank I want to replace it with No. any help is appreciated. -- Jerry Save your data and use a copy for this exercize........... Assuming your data in Column A, put this in B1 and copy down........ =IF(A1=1,"Yes","No") Then highlight the column and do Copy > PasteSpecial > Values to get rid of the formulas..........then delete column A if you wish....... Vaya con Dios, Chuck, CABGx3 "Jerry Arnone, ...
Which Function...
will allow me to calculate a value on a form depending on the information in two other fields? For instance: If ad_Advertiser can have 3 values Private party, Commercial or National and each has a different rate and the cost of an ad is dependent on the number of words in the ad, i would expect to write something like this: CCUR(IIF([ad_Advertiser]=Private party, 3.50+(([wordcount]-10)*.35); IIF([ad_Advertiser]=Commercial, 4.50+(([wordcount]-10)*.45); IIF([ad_Advertiser]=National, 6.50+(([wordcount]-10)*.65) I know that the IIF function is not correct but not sure what to use in its place...
Inserting dashes to an existing number
Hi, I am trying to create a formula that will divide this ten digit numbe into the following dashes. Here is the number 1234000999 I need the dashes to be inserted in thi format 1234-000-999. What is the appropriate formula. Thanks for your help. Dinahros -- dinahros ----------------------------------------------------------------------- dinahrose's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1334 View this thread: http://www.excelforum.com/showthread.php?threadid=26643 Why not just custom format right click on selection>format>number>custom...
Can't Convert Quicken 2001 Deluxe Data to MS Money 2004
First I tried to convert the Quicken Data using the MS Money 2003 OEM Version that came with the new computer I bought last week. Then I purchased MS Money 2004 Standard Edition and still couldn't convert my Quicken data. On the box, Microsoft says they offer a 30 day money back guarantee with no details about how to get your money back. Does anyone know how I can get my money back? Regards, John E. Golden ...
Number Rows on a report?
It would be anice addition if I could add numbers (a count) in front of each row on my filtered report. Any easy way to do this. I was thinking an unbound text box with something in the control source - I just don't know the something :) Any help here will be appreciated. Thanks in advance On Wed, 20 Jun 2007 14:07:27 -0700, Dave wrote: > It would be anice addition if I could add numbers (a count) in front of each > row on my filtered report. > > Any easy way to do this. > > I was thinking an unbound text box with something in the control source - I > just...
How do I convert csv to columns?
Can someone tell me (using Excel 2330) how I make a csv file which has semi colon separation points into columns? thanks After you open this file all of your data should be in column A, select this column and Data, Text to Columns, Delimited, by semicolon should give you the result you seek. plato Wrote: > Can someone tell me (using Excel 2330) how I make a csv file which has > semi > colon separation points into columns? > > thanks -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelfo...
XML File Converter for MS 2004
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: intel I'm a high school teacher. I write power points on MS 2004 on my Mac and transfer them to my school's PC that has MS 2003. This worked great, with exception of minor formatting changes, until a month ago. Power points that I previously opened on that PC, along with new ones, would not open - this is disasterous for instruction. My tech says 1) the school's PC may have had updates that caused the new problem and 2) that I should install the Open XML File Converter for Mac's MS 2004. I'm concerned tha...