Convert Text to numeric Value

[Dylan @ UAFC]

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

 

[Shane Devenshire]

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]

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

 

[Fred Smith]

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]

The A colum would have entries like
the
H1
H2
that would need to be replaced by
the mnumeric value in another colum n

 

[Gord Dibben]

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

 

[Max]

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]

VLOOKUP is what I was looking for
Works perfect
Thanks

 

[Max]

Good to hear. Welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik