| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup Error, how to convert Text to Number
I have a Table with foll Detail Which is used for Vlookup (This is created
by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
One way is to copy a blank cell, then right click on the id col of text
numbers psate special add ok. That should convert it all at one go to real numbers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
Thanks Max
It was a nice easy solution "Max" wrote: One way is to copy a blank cell, then right click on the id col of text numbers psate special add ok. That should convert it all at one go to real numbers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
welcome, glad it helped.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "claude jerry" wrote in message ... Thanks Max It was a nice easy solution |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
Hi,
You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
Yes ! that was something new I was looking for.
Just for my extra information. . How will I change a Text 1234 to Number 1234 I guess for this Max's trick works well Thanks both you guys "pinmaster" wrote: Hi, You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
Hi,
To change numbers formatted as text to real numbers you can use the ABS function: =ABS(A1) Regards! Jean-Guy "claude jerry" wrote: Yes ! that was something new I was looking for. Just for my extra information. . How will I change a Text 1234 to Number 1234 I guess for this Max's trick works well Thanks both you guys "pinmaster" wrote: Hi, You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
but
refering to some other context, If I have a Text value say '-235 and I want to convert this Text into Number -235 . . . =Abs() will , show convert the number as positive 235, "pinmaster" wrote: Hi, To change numbers formatted as text to real numbers you can use the ABS function: =ABS(A1) Regards! Jean-Guy "claude jerry" wrote: Yes ! that was something new I was looking for. Just for my extra information. . How will I change a Text 1234 to Number 1234 I guess for this Max's trick works well Thanks both you guys "pinmaster" wrote: Hi, You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
you are absolutely correct, the abs function will return a positive
number...maybe someone else has a solution for that. Regards! Jean-Giu "claude jerry" wrote: but refering to some other context, If I have a Text value say '-235 and I want to convert this Text into Number -235 . . . =Abs() will , show convert the number as positive 235, "pinmaster" wrote: Hi, To change numbers formatted as text to real numbers you can use the ABS function: =ABS(A1) Regards! Jean-Guy "claude jerry" wrote: Yes ! that was something new I was looking for. Just for my extra information. . How will I change a Text 1234 to Number 1234 I guess for this Max's trick works well Thanks both you guys "pinmaster" wrote: Hi, You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
How about:
=SIGN(A1)*ABS(A1) ? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "pinmaster" wrote in message ... you are absolutely correct, the abs function will return a positive number...maybe someone else has a solution for that. Regards! Jean-Giu "claude jerry" wrote: but refering to some other context, If I have a Text value say '-235 and I want to convert this Text into Number -235 . . . =Abs() will , show convert the number as positive 235, "pinmaster" wrote: Hi, To change numbers formatted as text to real numbers you can use the ABS function: =ABS(A1) Regards! Jean-Guy "claude jerry" wrote: Yes ! that was something new I was looking for. Just for my extra information. . How will I change a Text 1234 to Number 1234 I guess for this Max's trick works well Thanks both you guys "pinmaster" wrote: Hi, You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
Yes...also found this to work as well:
=IMREAL(A1) Regards! Jean-Guy "RagDyeR" wrote: How about: =SIGN(A1)*ABS(A1) ? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "pinmaster" wrote in message ... you are absolutely correct, the abs function will return a positive number...maybe someone else has a solution for that. Regards! Jean-Giu "claude jerry" wrote: but refering to some other context, If I have a Text value say '-235 and I want to convert this Text into Number -235 . . . =Abs() will , show convert the number as positive 235, "pinmaster" wrote: Hi, To change numbers formatted as text to real numbers you can use the ABS function: =ABS(A1) Regards! Jean-Guy "claude jerry" wrote: Yes ! that was something new I was looking for. Just for my extra information. . How will I change a Text 1234 to Number 1234 I guess for this Max's trick works well Thanks both you guys "pinmaster" wrote: Hi, You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
That does need the ATP however.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "pinmaster" wrote in message ... Yes...also found this to work as well: =IMREAL(A1) Regards! Jean-Guy "RagDyeR" wrote: How about: =SIGN(A1)*ABS(A1) ? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "pinmaster" wrote in message ... you are absolutely correct, the abs function will return a positive number...maybe someone else has a solution for that. Regards! Jean-Giu "claude jerry" wrote: but refering to some other context, If I have a Text value say '-235 and I want to convert this Text into Number -235 . . . =Abs() will , show convert the number as positive 235, "pinmaster" wrote: Hi, To change numbers formatted as text to real numbers you can use the ABS function: =ABS(A1) Regards! Jean-Guy "claude jerry" wrote: Yes ! that was something new I was looking for. Just for my extra information. . How will I change a Text 1234 to Number 1234 I guess for this Max's trick works well Thanks both you guys "pinmaster" wrote: Hi, You can also use a formula without changing the cells format, something like this: =VLOOKUP(TEXT(A1,"#"),table,2,0) Cheers! Jean-Guy "claude jerry" wrote: I have a Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id Name Date Fee 129 Tom 15/12/07 500 158 Kat 14/11/01 450 The above id's (129 and 158 etc ) are Text values I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id Name Date Fee 129 Blank Blank Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are in Text format and the Id's in the second table are Number. Vlookup gives an error. How can I convert these Numbers to text, or Text to Numbers. Format Cells Numbers . . .. does not help |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
On Dec 1, 1:55*am, Max wrote:
One way is to copy a blank cell, then right click on the id col oftextnumbers psate special add ok. That shouldconvertit all at one go to realnumbers. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- "claude jerry" wrote: I have a *Table with foll Detail Which is used for Vlookup (This is created by spooling data from a software package) Id * * * *Name * Date * * * * * *Fee 129 * *Tom * * * 15/12/07 * * *500 158 * * Kat * * * * *14/11/01 * * 450 The above id's (129 and 158 etc ) areTextvalues I.e if I use a test, =Istext(Id Cell) it gives me "True" Our head office sends me an excel File Id * * *Name * * * Date * * *Fee 129 * *Blank * * * Blank * *Blank The Id's they sent are in Number format I used a Test =Isnumber(Id cell) it gives me "True" I am using a VlookupFormula to fill the Blank information sent by Headoffice by refering to by data stored in the first table. but since in the Original table the Id's are inTextformat and the Id's in the second table are Number. Vlookup gives an error. How can IconverttheseNumberstotext, orTexttoNumbers. Format Cells Numbers. . .. does not help- Hide quotedtext- - Show quotedtext- Any suggestions, resources or perhaps solutions on how I can convert text into numbers on excel 2007? I have a column on my excel sheet and it has the text "Positive" and I want to convert that into 1? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
wrote
Any suggestions, resources or perhaps solutions on how I can convert text into numbers on excel 2007? I have a column on my excel sheet and it has the text "Positive" and I want to convert that into 1? You could try Edit Replace Steps in xl2003 (I don't know/have xl2007) Select the col Click Edit Replace, set it as: Find: Positive Replace with: 1 Click to Replace All -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
On Dec 22, 5:02*am, "Max" wrote:
wrote Any suggestions, resources or perhaps solutions on how I can convert text into numbers on excel 2007? I have a column on my excel sheet and it has the text "Positive" and I want to convert that into 1? You could try Edit Replace Steps in xl2003 (I don't know/have xl2007) Select the col Click Edit Replace, set it as: Find: Positive Replace with: 1 Click to Replace All -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- * Dude, you are the Man...I am not quite sure why I wasn't thinking more in a simple manner....... |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup Error, how to convert Text to Number
"Erlang" wrote
Dude, you are the Man...I am not quite sure why I wasn't thinking more in a simple manner....... Glad it helped. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert text to number | Excel Discussion (Misc queries) | |||
| convert number to text w/ error checking sign | Excel Worksheet Functions | |||
| Convert text number to number formate | Excel Discussion (Misc queries) | |||
| 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
