converting "text" numbers to numbers

I have been using the technique of multiplying cells that have number 
entries that behave like text entries by the value of 1 to convert them to 
numbers.  However, some mixed cell entries, convert strangely.  For example 
the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1.

Any explanation?  Any ideas on how to solve other than checking cell by cell 
to confirm all characters are numbers?

Confused!  Frustated!  <grin>.

Bob 


0
nospam2791 (369)
12/8/2008 2:57:46 AM
excel 39879 articles. 2 followers. Follow

6 Replies
878 Views

Similar Articles

[PageSpeed] 0

Just figured out the "why".  An entry like 6 A is converted to 6AM.  Its 
numerical value is 0.25.  Still hoping for a work around versus brute 
force....

Bob

"Bob Flanagan" <nospam@nospam.net> wrote in message 
news:fpOdneInIOezEKHUnZ2dnUVZ_s_inZ2d@giganews.com...
>I have been using the technique of multiplying cells that have number 
>entries that behave like text entries by the value of 1 to convert them to 
>numbers.  However, some mixed cell entries, convert strangely.  For example 
>the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1.
>
> Any explanation?  Any ideas on how to solve other than checking cell by 
> cell to confirm all characters are numbers?
>
> Confused!  Frustated!  <grin>.
>
> Bob
> 


0
nospam2791 (369)
12/8/2008 3:04:00 AM
The free ASAP Utilities supplies this function

"Bob Flanagan" <nospam@nospam.net> wrote in message 
news:fpOdneInIOezEKHUnZ2dnUVZ_s_inZ2d@giganews.com...
>I have been using the technique of multiplying cells that have number 
>entries that behave like text entries by the value of 1 to convert them to 
>numbers.  However, some mixed cell entries, convert strangely.  For example 
>the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1.
>
> Any explanation?  Any ideas on how to solve other than checking cell by 
> cell to confirm all characters are numbers?
>
> Confused!  Frustated!  <grin>.
>
> Bob
> 


0
ericNOSPAM (46)
12/8/2008 3:58:21 AM
Hi Bob

I think this short piece of code will do what you want.
Just mark the range of cells you want to convert and then run the code.

Sub converttonumber()
    Dim c As Range
    For Each c In Selection
        c.NumberFormat = "General"
        If c.Value <> "" Then
            If IsNumeric(c.Value) Then
               c.Value = Val(c.Value)
            End If
        End If
    Next
End Sub

-- 
Regards
Roger Govier

"Bob Flanagan" <nospam@nospam.net> wrote in message 
news:ZbCdndK1VNM-E6HUnZ2dnUVZ_szinZ2d@giganews.com...
> Just figured out the "why".  An entry like 6 A is converted to 6AM.  Its 
> numerical value is 0.25.  Still hoping for a work around versus brute 
> force....
>
> Bob
>
> "Bob Flanagan" <nospam@nospam.net> wrote in message 
> news:fpOdneInIOezEKHUnZ2dnUVZ_s_inZ2d@giganews.com...
>>I have been using the technique of multiplying cells that have number 
>>entries that behave like text entries by the value of 1 to convert them to 
>>numbers.  However, some mixed cell entries, convert strangely.  For 
>>example the entry "6 A" (without the quotes) becomes 0.25 when multiplied 
>>by 1.
>>
>> Any explanation?  Any ideas on how to solve other than checking cell by 
>> cell to confirm all characters are numbers?
>>
>> Confused!  Frustated!  <grin>.
>>
>> Bob
>>
>
> 
0
Roger
12/8/2008 9:57:21 AM
First toggle a setting.
Tools|Options|transition tab|check "transition formula evaluation"
(xl2003 menus)

Select an empty cell
Edit|copy
Select the offending range
edit|paste special Add (and values if you want)

And toggle that setting off.

I like to use an empty cell and add (instead of 1 and multiply) because empty
cells in the offending range aren't changed to 0.



Bob Flanagan wrote:
> 
> I have been using the technique of multiplying cells that have number
> entries that behave like text entries by the value of 1 to convert them to
> numbers.  However, some mixed cell entries, convert strangely.  For example
> the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1.
> 
> Any explanation?  Any ideas on how to solve other than checking cell by cell
> to confirm all characters are numbers?
> 
> Confused!  Frustated!  <grin>.
> 
> Bob

-- 

Dave Peterson
0
petersod (12004)
12/8/2008 12:45:56 PM
Dave, I will give your approach a try.  I think it will solve. The only 
concern is what else might get broken in the process <grin>.

I did a brute force solution, checking to make certain the cells contained 
just numbers before converting.  There weren't that many entries (about a 
1000) and the check went very fast.

Thanks everyone for the suggestions.

Bob

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:493D1704.37C981D3@verizonXSPAM.net...
> First toggle a setting.
> Tools|Options|transition tab|check "transition formula evaluation"
> (xl2003 menus)
>
> Select an empty cell
> Edit|copy
> Select the offending range
> edit|paste special Add (and values if you want)
>
> And toggle that setting off.
>
> I like to use an empty cell and add (instead of 1 and multiply) because 
> empty
> cells in the offending range aren't changed to 0.
>
>
>
> Bob Flanagan wrote:
>>
>> I have been using the technique of multiplying cells that have number
>> entries that behave like text entries by the value of 1 to convert them 
>> to
>> numbers.  However, some mixed cell entries, convert strangely.  For 
>> example
>> the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1.
>>
>> Any explanation?  Any ideas on how to solve other than checking cell by 
>> cell
>> to confirm all characters are numbers?
>>
>> Confused!  Frustated!  <grin>.
>>
>> Bob
>
> -- 
>
> Dave Peterson 


0
nospam2791 (369)
12/10/2008 1:36:02 PM
When I use any of those transition settings, I always turn them off as soon as
I'm done--even when I tested this suggestion.

I don't recall how Lotus works, so I'm not going to leave those settings on!

Bob Flanagan wrote:
> 
> Dave, I will give your approach a try.  I think it will solve. The only
> concern is what else might get broken in the process <grin>.
> 
> I did a brute force solution, checking to make certain the cells contained
> just numbers before converting.  There weren't that many entries (about a
> 1000) and the check went very fast.
> 
> Thanks everyone for the suggestions.
> 
> Bob
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:493D1704.37C981D3@verizonXSPAM.net...
> > First toggle a setting.
> > Tools|Options|transition tab|check "transition formula evaluation"
> > (xl2003 menus)
> >
> > Select an empty cell
> > Edit|copy
> > Select the offending range
> > edit|paste special Add (and values if you want)
> >
> > And toggle that setting off.
> >
> > I like to use an empty cell and add (instead of 1 and multiply) because
> > empty
> > cells in the offending range aren't changed to 0.
> >
> >
> >
> > Bob Flanagan wrote:
> >>
> >> I have been using the technique of multiplying cells that have number
> >> entries that behave like text entries by the value of 1 to convert them
> >> to
> >> numbers.  However, some mixed cell entries, convert strangely.  For
> >> example
> >> the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1.
> >>
> >> Any explanation?  Any ideas on how to solve other than checking cell by
> >> cell
> >> to confirm all characters are numbers?
> >>
> >> Confused!  Frustated!  <grin>.
> >>
> >> Bob
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12004)
12/10/2008 2:26:48 PM
Reply:

Similar Artilces:

Save as text file, but with full headers
I want to save quite a few messages as text file, but I want to save them with full headers. Is this possible? ...

Justify text across multiple columns
I have used Edit, Fill, Justify to fill rows with text within the boundaries of a set number of columns, but the text in each row is left justified. How do I justify the text (even spacing) in each row. If I highlight all of the text (multiple columns and rows) and then select Format, Cells, Alignment, Justify; Excel jams all the text into the first column. I know I could use a text box, but I'd prefer not to. Any help would be appreciated. Thanks. -Dave Hi Dave, Select the cells you want to center on. Under Format > Alignment > Horizontal drop down > Center across se...

Wrap text only when selected
Tricky question here . . . I have many text cells that I would prefer to have formatted with no text wrapping. That makes scanning the sheet for other data much easier on the eyes (fixed row height). However, I do need to see what the text is, but only on a line-by-line basis. I know that if I double-click on a cell, it will expand the text out. But what I'd really like to do is highlight the row (or a few rows) and have that apply text wrapping to each text cell in the region, and only for as long as it is highlighted. Can this even be done? Thanks, Lee It might become annoying, bu...

Text Box Defaults
Hi, Im trying to set a text box Default Value to a public variable in my database I have set the Default Value to = MyVariable(60) but all I get is #Name? the variable is a variant and I have checked to see that whats stored in the variable can be entered into the text box. Help! Steve - Land Down Under Steve, Is MyVariable the name of a Function? If so, I would expect it to work as the textbox's Default Value, as long as its syntax is correct. I would suspect that the #Name? error may not be related at all to this Default Value property. It is more likely to be related to the ...

Auto-Fit Text Box as Default
In Publisher, can 1 setup "Best-Fit" in textbox auto-fill as default?? As it stands now, I have to go in and change it "per" document, but would like to have this always on for all my new docs Thank you H wood I don't know a way that you can do that. I am confused by the statement that you have to change it "per" document since it is a change that is made per textbox. The problem with making text boxes best fit by default is that they can't be linked to by other text boxes. -- Computing should be about insight, not numbers or flash. "Hwood&...

Find/replace with different text colour messes up
When doing a Find/Replace on a certain word that needs to have a different colour than default - say, red - Excel incorrectly colours the whole cell instead of just the word that was searched on. To see this in action, try this: 1.. Open up a blank Excel sheet 2.. Enter some text in a few cells - "This is a test", for instance. Now, let's try to use search/replace to colour only the word "test" in red. 3.. Open up Search/Replace 4.. On the "Search for"-line, enter: test 5.. On the "Replace with"-line, enter: test 6.. For the "R...

pictures move with text?
I have a 138 page document with lots of pictures inserted. If I resize the pictures, how do I make subsequent pictures move when the text moves? A small child turns to Ed, and exclaims: "Look! Look! A post from Rosie Flannigan <anonymous@discussions.microsoft.com>!"... > I have a 138 page document with lots of pictures inserted. > If I resize the pictures, how do I make subsequent > pictures move when the text moves? You will need to make the pictures inline Right-click > Format Picture > Layout > Object Position = Inline -- Ed Bennett - MVP Microsoft Pub...

getting value from a cell into another cell behind a text string
Hi, I'd like to know how to get a value from a cell into another behind a text string by using a formula. For example, A B 1 20 Result (20) Thanks, Xianbo Use formatting Pull-down Format > Cells... > Number > Custom and the the field enter: "Result ("General")" The advantage of using formatting is that the cell can still be used for calculations -- Gary''s Student "Xianbo" wrote: > Hi, > > I'd like to know how to get a value from a cell into another behind a text > string by using a formula. Fo...

add text to back of post card
i created a post card in office 2007 place the clipart on the front and i need to know how to add business info on back thank you Create a new postcard publication. Print the front publication, turn the card over and print the back. With Publisher 2007 is this the least frustrating method. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "debbie" <debbie@discussions.microsoft.com> wrote in message news:9F74611D-57BD-48DB-A2FD-F7C282AED7D3@microsoft.com... >i created a post card in office 2007 place the clipart o...

How to import a tab delimited text file into MS Access database
Hi, I have the following task to be done in VC++ 6.0. I have a tab delimited text file that needs to be imported into a MS Access table. The table is created and the table structure is defined using DAO methods. So now I have to fill this table with the data in the text files. For a few rows in the text file, the number of columns could be less than the number of columns in the table. Can anyone please post me the code to do this. Thanks a ton in advance. Pradeep If you file has any indication of which data goes in which field you could just parse the CSV file and update the records one by...

Convert text to time format
Hey, I am unable to convert a piece of text that i copy paste from an external source to time format. Suppose I copy, "July 24 2006, 05:31 PM" and paste the same in excel it just assumes that that the entered data is text and I am unable to format the same. I need to convert this to the time format for proceeding with certain calculations. PLease help.:mad: :mad: +-------------------------------------------------------------------+ |Filename: TTT.zip | |Download: http://www.excelforum.com/attachment.php?postid=5111 | +-------...

Rotating Text
Using the device context, how can I rotate text? (its for the y axis on a graph) See if the following KB article offers any help http://support.microsoft.com/?id=154515 -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Chris Baker" <ChrisBaker@discussions.microsoft.com> wrote in message news:BD7C6EEB-2CDC-4EB8-B48D-509041C43593@microsoft.com... > Using the device context, how can I rotate text? > > (its for the y axis on a graph) > Chris Baker wrote: > Using the device context, how can I rotate text? > > (its for the ...

How to create a Pivot table from text file with more than 65000 rows?
Is there a way to create a pivot tabel where the source data is a tex file with more than 65000 rows of data? Normnally I just import th text file to excel and then create a pivot, but in this case I have to many rows but don't want to lose data. I guess I can use the create pivot tabel from external data sourc function but could somone take me through the steps to do this from large text file - if this is possible! Many Thanks -- morchar ----------------------------------------------------------------------- morchard's Profile: http://www.excelforum.com/member.php?action=getinf...

Currency to Text
I am creating invoices which show the Total as � currency. I need to add a further box that link to this currency No. and then shows it as Text eg �72 = seventy two pounds. The other part of this problem is I need the text to show as Nigerian "Naira" and "Kobo" as the main currency to 2 decimal places. Any help much appreciated in advance. Cheers see http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH Bob Phillips "sean" <sean.arthur@baesystems.com> wrote in message news:42b931c6$1_1@glkas0286.greenlnk.net... > I am creating invoices which...

Text import Wizard
Hi Guys Can someone help? I have an excel spreadsheet that when originally opened automatically activated the Convert Text to Columns Wizard. But now when opened it doesn't activate the Text Wizard even though the text is still in one column. Is it because Excel senses that the spreadsheet has already been through the conversion? Is there a way I can get Excel to automatically start this wizard when I open my spreadsheet. I look forward to hearing from you. Thank U 1. To activate the Convert Text-to-Col wizard manually: a. select the column b. click Data > Text to Columns ...

printing vertical text
Hi For my project I have to print a table with 5 rows and 5 colums. The text in the first row has to rotated over 90� so that the colums not get to wide. The first row has text and the rest of the table has 3 digit numbers. To print this I create a font for the first row and a different font for the rest of the table. The font for the first row I give the lforientation and lfescapement the value 900. This works a fine on my win2000 system where I develop the program but when I trie to use this on a win9x PC sometimes the orientation gets wrong. The text aperas not to be rotated 90� but -90�....

Layering Text Boxes with Word Art
In Publisher I used to be able to draw a text box over a portion of another text box, without the text in the first box moving. The same with WordArt - I could draw a WordArt box over a portion of a text box. Now when I do this, the text in the first box moves, defeating the effect I was trying to achieve in the first place. Is there any way to layer the boxes without having things move all over? A small child turns to Ed, and exclaims: "Look! Look! A post from jeanie-bee <jeanie-bee@discussions.microsoft.com>!"... > In Publisher I used to be able to draw a text box...

Pass control name from Access to Word and save text in the control
I've posted this twice in Access forms coding and had no luck. Maybe someone here can help me. Thanks. Novice Word 2003, Access 2003, XP Pro I have some four subforms each with a command button that opens a document in MS Word and renames it. The user then enters or adds text to the Word document. When they close Word, I want to save the text in the Word document into a memo field in Access. I don't know how to pass the control name to MS Word and then save the text back to that control. I don't want to use the form names so that the subforms can be used in ...

macro that finds text and keeps only part of it
I have a spreadsheet that I'm trying to run a seach on to find a specific sting of test. I can run the search but, now I want to ake it one step further. Is it possible to search for a sting of text and then delete part of the sting and leave some of the text? Then is it possible to do this a macro? Thanks for the help. Find and replace won't work? ************ Anne Troy www.OfficeArticles.com "john mcmichael" <johnmcmichael@discussions.microsoft.com> wrote in message news:E213CDBC-571B-4CCB-B925-FD95F4DF0CD9@microsoft.com... >I have a spreadsheet that I...

Capitalising text
When there is a column of text how do I change the text in the whole column to UPPERCASE in one step? If you're not too particular about an extra 2-3 steps .. Assume col A is the col of text in A1 down Put in B1: =TRIM(UPPER(A1)) Copy down Copy col B and then right click on col A Choose paste special > values > OK to overwrite col A Delete col B -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Winsome" <Winsome@discussions.microsoft.com> wrote in message news:D289F46E-3DE7-47A3-9584-63247CF4F18F@microsoft...

My object cannot be sent behind the text
Hi I'm trying to send an object behind the text, the Draw/Order/Send to Back option is available but when I select it tye object is still in front of the text. Any suggestions? ...

publisher could not recognize my text, plse help
my text is made 100% by arabic symbols, when i pase it from word into publisher i can not read it, i ll be happy if you could fix me this problem. thanks million How about saving the Word document as text and importing into Publisher, does that work? Can you paste into WordPad and retain the Arabic symbols? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "mooad2005" <mooad2005@discussions.microsoft.com> wrote in message news:3CDEF1F5-5FF8-4060-A058-1C04FAC3B829@microsoft.com... > my text is made 100% by arabic sy...

How do I soften or blurr the edges of an image or text box?
I am working in Publisher and need to know how to blur or soften the edges of an image or text box - is it possible? If you have Word 2007 you can blur the edges; copy/paste into Publisher. Otherwise you need to take your image into an editing program. -- Mary Sauer http://msauer.mvps.org/ "Luann" <Luann @discussions.microsoft.com> wrote in message news:213BE39B-94DB-403C-A4E6-86BBAAAA795B@microsoft.com... >I am working in Publisher and need to know how to blur or soften the edges of > an image or text box - is it possible? Thanks, that is what I was thinking, ...

text to speech stopped working in excel
Text to speech in Excel stopped working. Have Sapi 4 Have lhsp Have TTS3000 Get message when opening speech propetries that "The requested task cannot be carried out because the necessary engine could not be created. Please select a different engine and/or a differnt audio device" however there is only one engine listed in speech properties - "Microsft English ASR Version 5 Engine"????? The information in the following MSKB article may help: FIX: Speech engines do not work and you receive an error message when you open Speech Control Panel http://support.microsoft....

Operation of Asian text font selection in the Font dialog
I have a document that has been translated into Japanese, in which all of the translated text appears in MS Gothic font. This displays most of the characters correctly, but not all. Apparently, they are correct if the font is changed to MS PMincho. I'm trying to do this globally by changing the Font settings in the various styles, but no matter what I set in the Asian Text Font and Font boxes, the Japanese text remains stuck on MS Gothic, although any latin text changes to PMincho. I can apply PMincho as a character style, but that's tedious. How do I get the required ...