| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to convert text to number
please help
|
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
Give more details. Do you mean a word like "two" to be converted to
the number 2 ? Pete On Jul 31, 2:57*pm, kev wrote: please help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
So you have a bunch of numbers that are actually text??? If so then...
Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
I tried that and still didn't get numbers. I tried multiplying by one...no
luck. I then noticed that there were many empty spaces after each number. I tried the trim function and the value function. Eventually I just individually deleted the empty spaces and they turned into numbers. Fortunately I only had 6 cells to work with. Is there something else I could have tried? "Jim Thomlinson" wrote: So you have a bunch of numbers that are actually text??? If so then... Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
You could have used th formula =value() that converts any string containing a
number into number. -- Alex *Remember to click "yes" if this post helped you. Thank you! "gwen" wrote: I tried that and still didn't get numbers. I tried multiplying by one...no luck. I then noticed that there were many empty spaces after each number. I tried the trim function and the value function. Eventually I just individually deleted the empty spaces and they turned into numbers. Fortunately I only had 6 cells to work with. Is there something else I could have tried? "Jim Thomlinson" wrote: So you have a bunch of numbers that are actually text??? If so then... Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
Yes I tried that and it did not work.
"alexrs2k" wrote: You could have used th formula =value() that converts any string containing a number into number. -- Alex *Remember to click "yes" if this post helped you. Thank you! "gwen" wrote: I tried that and still didn't get numbers. I tried multiplying by one...no luck. I then noticed that there were many empty spaces after each number. I tried the trim function and the value function. Eventually I just individually deleted the empty spaces and they turned into numbers. Fortunately I only had 6 cells to work with. Is there something else I could have tried? "Jim Thomlinson" wrote: So you have a bunch of numbers that are actually text??? If so then... Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
Maybe your spaces weren't proper spaces (with character code of 32) -
they may have been the non-breaking space character with a code of 160, and TRIM does not get rid of these. You would need to use the SUBSTITUTE function, or you could use Find & Replace. Hope this helps. Pete On Jul 31, 8:32*pm, gwen wrote: Yes I tried that and it did not work. "alexrs2k" wrote: You could have used th formula =value() that converts any string containing a number into number. -- Alex *Remember to click "yes" if this post helped you. Thank you! "gwen" wrote: I tried that and still didn't get numbers. *I tried multiplying by one...no luck. *I then noticed that there were many empty spaces after each number. *I tried the trim function and the value function. *Eventually I just individually deleted the empty spaces and they turned into numbers. * Fortunately I only had 6 cells to work with. *Is there something else I could have tried? "Jim Thomlinson" wrote: So you have a bunch of numbers that are actually text??? If so then.... Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
How do I tell what character code they are? How would I use Substitute or Find and replace? "Pete_UK" wrote: Maybe your spaces weren't proper spaces (with character code of 32) - they may have been the non-breaking space character with a code of 160, and TRIM does not get rid of these. You would need to use the SUBSTITUTE function, or you could use Find & Replace. Hope this helps. Pete On Jul 31, 8:32 pm, gwen wrote: Yes I tried that and it did not work. "alexrs2k" wrote: You could have used th formula =value() that converts any string containing a number into number. -- Alex *Remember to click "yes" if this post helped you. Thank you! "gwen" wrote: I tried that and still didn't get numbers. I tried multiplying by one...no luck. I then noticed that there were many empty spaces after each number. I tried the trim function and the value function. Eventually I just individually deleted the empty spaces and they turned into numbers. Fortunately I only had 6 cells to work with. Is there something else I could have tried? "Jim Thomlinson" wrote: So you have a bunch of numbers that are actually text??? If so then.... Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
Saved from a previous post.
Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"") Replace ## with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(##)) '<--What showed up in CellView? myGoodChars = Array("") If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) gwen wrote: How do I tell what character code they are? How would I use Substitute or Find and replace? "Pete_UK" wrote: Maybe your spaces weren't proper spaces (with character code of 32) - they may have been the non-breaking space character with a code of 160, and TRIM does not get rid of these. You would need to use the SUBSTITUTE function, or you could use Find & Replace. Hope this helps. Pete On Jul 31, 8:32 pm, gwen wrote: Yes I tried that and it did not work. "alexrs2k" wrote: You could have used th formula =value() that converts any string containing a number into number. -- Alex *Remember to click "yes" if this post helped you. Thank you! "gwen" wrote: I tried that and still didn't get numbers. I tried multiplying by one...no luck. I then noticed that there were many empty spaces after each number. I tried the trim function and the value function. Eventually I just individually deleted the empty spaces and they turned into numbers. Fortunately I only had 6 cells to work with. Is there something else I could have tried? "Jim Thomlinson" wrote: So you have a bunch of numbers that are actually text??? If so then.... Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to convert text to number
Perfect. I used
= value(substitute(c29, char(160),"")) after using the Addin to determine the what unseen character code I had. Thank-you. "Dave Peterson" wrote: Saved from a previous post. Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"") Replace ## with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(##)) '<--What showed up in CellView? myGoodChars = Array("") If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) gwen wrote: How do I tell what character code they are? How would I use Substitute or Find and replace? "Pete_UK" wrote: Maybe your spaces weren't proper spaces (with character code of 32) - they may have been the non-breaking space character with a code of 160, and TRIM does not get rid of these. You would need to use the SUBSTITUTE function, or you could use Find & Replace. Hope this helps. Pete On Jul 31, 8:32 pm, gwen wrote: Yes I tried that and it did not work. "alexrs2k" wrote: You could have used th formula =value() that converts any string containing a number into number. -- Alex *Remember to click "yes" if this post helped you. Thank you! "gwen" wrote: I tried that and still didn't get numbers. I tried multiplying by one...no luck. I then noticed that there were many empty spaces after each number. I tried the trim function and the value function. Eventually I just individually deleted the empty spaces and they turned into numbers. Fortunately I only had 6 cells to work with. Is there something else I could have tried? "Jim Thomlinson" wrote: So you have a bunch of numbers that are actually text??? If so then.... Place the number 1 in any blank cell. Copy it Select the range of text to be Converted Select Paste Special - Multiply Your text is now numbers... -- HTH... Jim Thomlinson "kev" wrote: please help- Hide quoted text - - Show quoted text - -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to convert number to text | Excel Worksheet Functions | |||
| Convert text number to number formate | Excel Discussion (Misc queries) | |||
| Number convert in to text ? | Excel Worksheet Functions | |||
| convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
| not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions | |||
Linear Mode
