Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
I am trying to convert a string of text into a numeric value.
Ie, I have several code legends to report results that need to have a money value. H1=154.00 H2=254.00 I would like to be able to type either of these code legeds in say A1 and then they will return the numeric value in say A45 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
Hi,
Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. Cheers, Shane Devenshire "Dylan @ UAFC" wrote: I am trying to convert a string of text into a numeric value. Ie, I have several code legends to report results that need to have a money value. H1=154.00 H2=254.00 I would like to be able to type either of these code legeds in say A1 and then they will return the numeric value in say A45 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
Maybe i am askign the question wrong, I know some to
very little about VBA codes, a few simple tricks, but we have used macros to try and solve the problem by a recording a seris of comands. we are currently highlighting an entire colum of these various legend codes and pasting into the desired column were this code need to have a numeric value for accoutning purposes, and then through the editing tab replacing the H1 with 154.05, H2 with 245.05, ect.) I am thining there has be a formula I could entire into those cells that would replace the serios of legend codes with the accouting values. Please advise "Shane Devenshire" wrote: Hi, Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. Cheers, Shane Devenshire "Dylan @ UAFC" wrote: I am trying to convert a string of text into a numeric value. Ie, I have several code legends to report results that need to have a money value. H1=154.00 H2=254.00 I would like to be able to type either of these code legeds in say A1 and then they will return the numeric value in say A45 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
If your text cell looks like a number, you can conert it using the Value
function. So in A45, you would use: =value(a1) However, if your cell has "H1=154.00" in it, you need to parse out the text. In this case, you need to tell us: is it allways the last 6 characters? Or do you want everything after the equal sign? or something else? If it's always the characters after the equal sign, use: =value(mid(a1,find("=",a1)+1,20)) Regards, Fred. "Dylan @ UAFC" wrote in message ... I am trying to convert a string of text into a numeric value. Ie, I have several code legends to report results that need to have a money value. H1=154.00 H2=254.00 I would like to be able to type either of these code legeds in say A1 and then they will return the numeric value in say A45 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
The A colum would have entries like
the H1 H2 that would need to be replaced by the mnumeric value in another colum n "Fred Smith" wrote: If your text cell looks like a number, you can conert it using the Value function. So in A45, you would use: =value(a1) However, if your cell has "H1=154.00" in it, you need to parse out the text. In this case, you need to tell us: is it allways the last 6 characters? Or do you want everything after the equal sign? or something else? If it's always the characters after the equal sign, use: =value(mid(a1,find("=",a1)+1,20)) Regards, Fred. "Dylan @ UAFC" wrote in message ... I am trying to convert a string of text into a numeric value. Ie, I have several code legends to report results that need to have a money value. H1=154.00 H2=254.00 I would like to be able to type either of these code legeds in say A1 and then they will return the numeric value in say A45 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
So what you're saying is that H1 and H2 are not cell addresses?
They are codes and you want to type in the code H1 and have 154.05 returned? Either a LOOKUP table and a helper column or worksheet event code would do that. Something like this in a helper cell for the LOOKUP formula................ =LOOKUP(B1,{"H1","H2","H3"},{154.05,254.05,354.05} ) Event code to make the change in-cell when the code is entered Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:A100") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("H1", "H2", "H3") nums = Array(154.05, 245.05, 345.05) For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then rr.Value = ival End If Next End Sub Right-click on the sheet tab and "View Code". Copy/paste into that module. Enter codes in column A to have values returned. If many codes, we could modify to use a range for the vals and nums. Gord Dibben MS Excel MVP On Mon, 24 Nov 2008 16:59:01 -0800, Dylan @ UAFC wrote: Maybe i am askign the question wrong, I know some to very little about VBA codes, a few simple tricks, but we have used macros to try and solve the problem by a recording a seris of comands. we are currently highlighting an entire colum of these various legend codes and pasting into the desired column were this code need to have a numeric value for accoutning purposes, and then through the editing tab replacing the H1 with 154.05, H2 with 245.05, ect.) I am thining there has be a formula I could entire into those cells that would replace the serios of legend codes with the accouting values. Please advise "Shane Devenshire" wrote: Hi, Problem: When numbers are enter as text they may not calculate within formulas as they should. A few formulas will work fine despite the numbers being entered as text. Numbers can be stored as text by 1. preformatting the cell to Text and entering the number, 2. Typing an apostrophy in front of the number '123, 3. Because the data was downloaded from a soure inwhich it was stored as a number, 4. Because you used the Text to Columns command and converted it to text., and.... There is no sure indicator that a number is stored as text, although numbers are usually right aligned and text left aligned, this may not be the case. If you are using a later version of Excel, Error Checking green triangles may appear at the top left corner of these cell, but this feature may be off or the version of Excel may not support it. (2000 and earlier). You can find out what data type the entries are by using the =ISTEXT(A1) or =ISNUMBER(A1) functions. You can not tell by checking the Format. If a number was entered in a cell preformatted as General or as a number, then it will be a number, even if it's current format is Text. Likewise a number entered in a cell preformatted as Text will be text even if it's current format is Number, General, Date, Currency and the like. Solution: 1. Change the format to one that is numeric and then reenter the numbers (too slow and error prone.) 2. Select the cells and open the Error Checking options and choose Convert to Numbers. 3. Select an empty cell and copy it. Select the text number cell and choose Edit, Paste Special, Add (or Subtract). This method is ~100 times faster than #2. Dates are numbers, and if they are stored as text, you will not get an Error Checking triangle, so method #3 is obligatory if there is a substantial number of dates to convert. If this information is helpful, please click the Yes button. Cheers, Shane Devenshire "Dylan @ UAFC" wrote: I am trying to convert a string of text into a numeric value. Ie, I have several code legends to report results that need to have a money value. H1=154.00 H2=254.00 I would like to be able to type either of these code legeds in say A1 and then they will return the numeric value in say A45 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
Maybe a simple vlookup could do it for you ..
Assume you've got this reference table listed in Sheet2's cols A and B: H1 154 H2 254 etc Then in another sheet, Listed in A2 down are the text eg: H1, H2,... You could place this in say, N2: =IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0)) and copy N2 down to return the nums corresponding to H1, H2, etc -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Dylan @ UAFC" wrote: The A colum would have entries like the H1 H2 that would need to be replaced by the mnumeric value in another colum n |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
VLOOKUP is what I was looking for
Works perfect Thanks "Max" wrote: Maybe a simple vlookup could do it for you .. Assume you've got this reference table listed in Sheet2's cols A and B: H1 154 H2 254 etc Then in another sheet, Listed in A2 down are the text eg: H1, H2,... You could place this in say, N2: =IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0)) and copy N2 down to return the nums corresponding to H1, H2, etc -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Dylan @ UAFC" wrote: The A colum would have entries like the H1 H2 that would need to be replaced by the mnumeric value in another colum n |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Text to numeric Value
Good to hear. Welcome.
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Dylan @ UAFC" wrote in message ... VLOOKUP is what I was looking for Works perfect Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to convert a numeric value in text | Excel Worksheet Functions | |||
Convert text to numeric value | Excel Discussion (Misc queries) | |||
Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
convert numeric to text | Excel Worksheet Functions |