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

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

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

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

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

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

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

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

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

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

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

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

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?

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
---

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.......

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
---