6 Free Financial Modeling Lessons...and More.

Enter your e-mail below to get our new Free Modeling Tutorials ($200+ Value).

< previous | next >

Excel Text to number conversion
 

trade4size's picture
ST
(Neanderthal, 2,085
 
Points)
 on 2/16/08 at 11:51pm

First some background

So I got bored and decided I wanted to look for sites that graphically tracked the change in a stocks EPS over time. I really want to track atleast 5 years worth of data so I have enough data points. www.earnings.com gives me the necessary data however when i cut and paste the earnings history into excel for some reason the earnings figures are pasted as text or something that doesnt allow me to treat them as I otherwise a number.

For example

A1= $0.27
A2= $0.35

I want to add A1 and A2, however when i do it simply displays it as #VALUE

I have tryed using text to column, I have tryed multiplying the selected cells by 1, and i have even tryed the following macro.

Sub Enter_Values()
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell
End Sub

How do I convert these so i can run operations on the data?! Thanks.

"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.
Tags:
trade4size's picture

After playing around with it

ST
(Neanderthal, 2,085
 
Points)
 on 2/17/08 at 12:16am

After playing around with it some more it appears that what is causing the problem is a space at the end of the string. For some reason when i use the TRIM function it still does not work correctly. Again any help would greatly be appreciated. I have ran into this problem many times and I absolutely hate having go through my hand and retype it.

"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.

Cest's picture

create an array that'll take

O
(Baboon, 109
 
Points)
 on 2/17/08 at 12:33am

create an array that'll take that data and then cycle it through a For loop each time declaring it as Integer or Long since now it seems to be a String type of variable. Not 100% if this'll work but give it a shot

quick google result:
http://www.contextures.com/xlDataEntry03.html

trade4size's picture

I actually found a macro to

ST
(Neanderthal, 2,085
 
Points)
 on 2/17/08 at 2:12am

I actually found a macro to remove all spaces. It worked.

"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.

computerblue's picture

trade4size, where did u find

O
 
Points)
 on 2/17/08 at 12:07pm

trade4size, where did u find the macro?

trade4size's picture

http://www.mvps.org/dmcritchi

ST
(Neanderthal, 2,085
 
Points)
 on 2/17/08 at 1:38pm

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Used this code

Sub RemoveAllSpaces()
'David McRitchie 2000-10-24
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.SpecialCells(xlConstants).Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Selection.SpecialCells(xlConstants).Replace What:=Chr(32), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Pretty helpfull feature, if you need to pull large amounts of data from tables on web sites and when you cut and paste it comes out as HTML and not numbers this will fix the problem. This will save me hours.

Now i just cant figure out how to save the macro for general use. since it will be something i do pretty common.

"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.

LondonE11's picture

Sub ConvertToNum() Dim

IB
(Gorilla, 525
 
Points)
 on 2/19/08 at 9:59am

Sub ConvertToNum()
Dim bCell as Range, TheNum as String
For Each bCell in Selection
TheNum = Replace(Range(bCell).value, " ", "")
TheNum = Replace(TheNum,"$", "")
Range(bCell).value = cdbl(TheNum)
Next bCell
End Sub

Assign an easy short-cut key to your macro, makes it alot easier. That should work on every cell in the range you select.

LondonE11's picture

also try doing a Web Query

IB
(Gorilla, 525
 
Points)
 on 2/19/08 at 11:52am

also try doing a Web Query from Yahoo finance, it'll save you the cut & paste and can directly import it in a number format into an Excel worksheet.

fordhammasta's picture

T4S, you should be either be

IB
 
Points)
 on 2/19/08 at 12:00pm

T4S, you should be either be drinking or moving forum topics instead of doing this :)...btw how is your internship search going? shoot me a PM.

trade4size's picture

Elan- I cant figure out how

ST
(Neanderthal, 2,085
 
Points)
 on 2/20/08 at 12:06am

Elan- I cant figure out how to assign a short cut key to the macro. Im still very new to VBA and macros but as i tend to need more features i learn new things.

Stern- I was drinking when i did this.... (excel)MODELS & Bottles BABY! Ohhhh yeah.

"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.

LondonE11's picture

assigning shortcut key to macro

IB
(Gorilla, 525
 
Points)
 on 2/22/08 at 1:42pm

ALT+T+M+M or Alt+F8
or goto TOOLS in the menu, then MACRO then MACROS

Once the Macro dialog window comes up, select the macro you want to assign a shortcut key to and then click the OPTIONS button or hit ALT+O. This will let you assign a shortcut key to the macro. VERY VERY helpful.

CTRL+ whatever key you assign

trade4size:

Elan- I cant figure out how to assign a short cut key to the macro. Im still very new to VBA and macros but as i tend to need more features i learn new things.

Stern- I was drinking when i did this.... (excel)MODELS & Bottles BABY! Ohhhh yeah.

KMM's picture

What is a macro, when would you use them?

IB
(Baboon, 128
 
Points)
 on 2/22/08 at 2:02pm

Will banking analysts be expected to do this? If so, I am screwed lol.

LondonE11's picture

not that hard to learn

IB
(Gorilla, 525
 
Points)
 on 2/22/08 at 2:35pm

Get Excel VBA Programming for Dummies.

Its very easy and straightforward. Dont be intimidated cuz it looks like programming, it barely is.

KMM:

Will banking analysts be expected to do this? If so, I am screwed lol.

LondonE11's picture

just tried my code

IB
(Gorilla, 525
 
Points)
 on 2/22/08 at 4:31pm

Correction:

Sub ConvertToNum()
Dim bCell As Range, TheNum As String
For Each bCell In Selection
TheNum = Replace(bCell, " ", "")
TheNum = Replace(TheNum, "$", "")
bCell.Value = CDbl(TheNum)
bCell.NumberFormat = "0.00"
Next bCell
End Sub

elan:

Sub ConvertToNum()
Dim bCell as Range, TheNum as String
For Each bCell in Selection
TheNum = Replace(Range(bCell).value, " ", "")
TheNum = Replace(TheNum,"$", "")
Range(bCell).value = cdbl(TheNum)
Next bCell
End Sub

Assign an easy short-cut key to your macro, makes it alot easier. That should work on every cell in the range you select.

trade4size's picture

hmmm i know how to assign

ST
(Neanderthal, 2,085
 
Points)
 on 2/23/08 at 3:28am

hmmm i know how to assign shortcuts using that method. Problem is when i close my workbook and go to my macros none are there. Fortunately VBA saves them so i just apply the macros i need to the specific workbook. Im sure what im trying to so is so simple to do I just cant explain what it is I am trying to do. I dont know VBA or macros I just typically know how to get whatever it is I need to do.

"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.

6 Free Financial Modeling Lessons...Straight to Your Inbox.

Confirm your name and e-mail below to get our best tips

Monkey Stats
Account information
We respect your Name and E-mail privacy. By joining you accept our terms of service.