ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Concantenate numeric fields (https://www.excelbanter.com/excel-worksheet-functions/126237-concantenate-numeric-fields.html)

Pa Maher
Concantenate numeric fields
 
A report lists in 2 columns an account number and a sub-account number as
numeric fields.
I want to concantenate these fields. when I use "=CONCATENATE(B7,C7)" I get
a #VALUE! error message. If I place a ' infront of the numbers in B7 and C7
then it works fine. Is there any way to use the concantenate function
without inserting the ' infront of a few thousand fields? Changing the cell
format to text has not worked.
Thanks

CLR
Concantenate numeric fields
 
The CONCATENTION works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account number as
numeric fields.
I want to concantenate these fields. when I use "=CONCATENATE(B7,C7)" I
get
a #VALUE! error message. If I place a ' infront of the numbers in B7 and
C7
then it works fine. Is there any way to use the concantenate function
without inserting the ' infront of a few thousand fields? Changing the
cell
format to text has not worked.
Thanks

Pa Maher
Concantenate numeric fields
 
XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account number as
numeric fields.
I want to concantenate these fields. when I use "=CONCATENATE(B7,C7)" I
get
a #VALUE! error message. If I place a ' infront of the numbers in B7 and
C7
then it works fine. Is there any way to use the concantenate function
without inserting the ' infront of a few thousand fields? Changing the
cell
format to text has not worked.
Thanks

CLR
Concantenate numeric fields
 
I dunno........the numbers CONCATENATE fine for me on this, another 97
machine. I don't have 2003, so maybe someone with it will reply as to how
it reacts for them.........sorry I could not be of more help.

Vaya con Dios,
Chuck, CABGx3




"Pa Maher" wrote in message
...
XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account number
as
numeric fields.
I want to concantenate these fields. when I use "=CONCATENATE(B7,C7)"
I
get
a #VALUE! error message. If I place a ' infront of the numbers in B7
and
C7
then it works fine. Is there any way to use the concantenate function
without inserting the ' infront of a few thousand fields? Changing
the
cell
format to text has not worked.
Thanks

Dave Peterson
Concantenate numeric fields
 
Try
Tools|Options|Transition Tab|Uncheck "Transition formula evaluation"

If you're a plain old excel user, you may want to uncheck all those options.

If you've come from using Lotus 123 and like that behavior, then

=concatenate(Text(B7,"0"),Text(C7,"0"))
or just
=Text(B7,"0")&Text(C7,"0")

(use the number format that applies)

Pa Maher wrote:

XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account number as
numeric fields.
I want to concantenate these fields. when I use "=CONCATENATE(B7,C7)" I
get
a #VALUE! error message. If I place a ' infront of the numbers in B7 and
C7
then it works fine. Is there any way to use the concantenate function
without inserting the ' infront of a few thousand fields? Changing the
cell
format to text has not worked.
Thanks




--

Dave Peterson

Gord Dibben
Concantenate numeric fields
 
Chuck and Pa

Excel 2003............

No problem with =B7&B8 with the sample given below.

Returns 12345610 as text

=CONCATENATE(B7,B8)

Returns 12345610 as text

=(B7&B8)*1 returns 12345610 as numeric


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 19:47:32 -0500, "CLR" wrote:

I dunno........the numbers CONCATENATE fine for me on this, another 97
machine. I don't have 2003, so maybe someone with it will reply as to how
it reacts for them.........sorry I could not be of more help.

Vaya con Dios,
Chuck, CABGx3




"Pa Maher" wrote in message
...
XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account number
as
numeric fields.
I want to concantenate these fields. when I use "=CONCATENATE(B7,C7)"
I
get
a #VALUE! error message. If I place a ' infront of the numbers in B7
and
C7
then it works fine. Is there any way to use the concantenate function
without inserting the ' infront of a few thousand fields? Changing
the
cell
format to text has not worked.
Thanks

CLR
Concantenate numeric fields
 
Well then..........in light of what Gord says, I usggest the OP try two
things.......one, create a brand new file and try the
CONCATENATION...........second, try another machine with the same old file
and see if the problem still goes with the file to the new machine........

Vaya con Dios,
Chuck, CABGx3


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Chuck and Pa

Excel 2003............

No problem with =B7&B8 with the sample given below.

Returns 12345610 as text

=CONCATENATE(B7,B8)

Returns 12345610 as text

=(B7&B8)*1 returns 12345610 as numeric


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 19:47:32 -0500, "CLR"
wrote:

I dunno........the numbers CONCATENATE fine for me on this, another 97
machine. I don't have 2003, so maybe someone with it will reply as to
how
it reacts for them.........sorry I could not be of more help.

Vaya con Dios,
Chuck, CABGx3




"Pa Maher" wrote in message
...
XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account
number
as
numeric fields.
I want to concantenate these fields. when I use
"=CONCATENATE(B7,C7)"
I
get
a #VALUE! error message. If I place a ' infront of the numbers in
B7
and
C7
then it works fine. Is there any way to use the concantenate
function
without inserting the ' infront of a few thousand fields? Changing
the
cell
format to text has not worked.
Thanks

Gord Dibben
Concantenate numeric fields
 
See Dave's post about the Transition keys.

He was bang on with that.


Gord

On Tue, 16 Jan 2007 20:22:30 -0500, "CLR" wrote:

Well then..........in light of what Gord says, I usggest the OP try two
things.......one, create a brand new file and try the
CONCATENATION...........second, try another machine with the same old file
and see if the problem still goes with the file to the new machine........

Vaya con Dios,
Chuck, CABGx3


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Chuck and Pa

Excel 2003............

No problem with =B7&B8 with the sample given below.

Returns 12345610 as text

=CONCATENATE(B7,B8)

Returns 12345610 as text

=(B7&B8)*1 returns 12345610 as numeric


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 19:47:32 -0500, "CLR"
wrote:

I dunno........the numbers CONCATENATE fine for me on this, another 97
machine. I don't have 2003, so maybe someone with it will reply as to
how
it reacts for them.........sorry I could not be of more help.

Vaya con Dios,
Chuck, CABGx3




"Pa Maher" wrote in message
...
XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account
number
as
numeric fields.
I want to concantenate these fields. when I use
"=CONCATENATE(B7,C7)"
I
get
a #VALUE! error message. If I place a ' infront of the numbers in
B7
and
C7
then it works fine. Is there any way to use the concantenate
function
without inserting the ' infront of a few thousand fields? Changing
the
cell
format to text has not worked.
Thanks

CLR
Concantenate numeric fields
 
I'll be durned.........I just checked it out in 97 and it even works that
way here..........
Hats off to Dave..........

Vaya con Dios,
Chuck, CABGx3



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
See Dave's post about the Transition keys.

He was bang on with that.


Gord

On Tue, 16 Jan 2007 20:22:30 -0500, "CLR"
wrote:

Well then..........in light of what Gord says, I usggest the OP try two
things.......one, create a brand new file and try the
CONCATENATION...........second, try another machine with the same old
file
and see if the problem still goes with the file to the new
machine........

Vaya con Dios,
Chuck, CABGx3


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Chuck and Pa

Excel 2003............

No problem with =B7&B8 with the sample given below.

Returns 12345610 as text

=CONCATENATE(B7,B8)

Returns 12345610 as text

=(B7&B8)*1 returns 12345610 as numeric


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 19:47:32 -0500, "CLR"
wrote:

I dunno........the numbers CONCATENATE fine for me on this, another 97
machine. I don't have 2003, so maybe someone with it will reply as to
how
it reacts for them.........sorry I could not be of more help.

Vaya con Dios,
Chuck, CABGx3




"Pa Maher" wrote in message
...
XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account
number
as
numeric fields.
I want to concantenate these fields. when I use
"=CONCATENATE(B7,C7)"
I
get
a #VALUE! error message. If I place a ' infront of the numbers
in
B7
and
C7
then it works fine. Is there any way to use the concantenate
function
without inserting the ' infront of a few thousand fields?
Changing
the
cell
format to text has not worked.
Thanks

Dave Peterson
Concantenate numeric fields
 
Put that hat back on!

It's too cold out there without a hat.

CLR wrote:

I'll be durned.........I just checked it out in 97 and it even works that
way here..........
Hats off to Dave..........

Vaya con Dios,
Chuck, CABGx3

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
See Dave's post about the Transition keys.

He was bang on with that.


Gord

On Tue, 16 Jan 2007 20:22:30 -0500, "CLR"
wrote:

Well then..........in light of what Gord says, I usggest the OP try two
things.......one, create a brand new file and try the
CONCATENATION...........second, try another machine with the same old
file
and see if the problem still goes with the file to the new
machine........

Vaya con Dios,
Chuck, CABGx3


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Chuck and Pa

Excel 2003............

No problem with =B7&B8 with the sample given below.

Returns 12345610 as text

=CONCATENATE(B7,B8)

Returns 12345610 as text

=(B7&B8)*1 returns 12345610 as numeric


Gord Dibben MS Excel MVP

On Tue, 16 Jan 2007 19:47:32 -0500, "CLR"
wrote:

I dunno........the numbers CONCATENATE fine for me on this, another 97
machine. I don't have 2003, so maybe someone with it will reply as to
how
it reacts for them.........sorry I could not be of more help.

Vaya con Dios,
Chuck, CABGx3




"Pa Maher" wrote in message
...
XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account
number
as
numeric fields.
I want to concantenate these fields. when I use
"=CONCATENATE(B7,C7)"
I
get
a #VALUE! error message. If I place a ' infront of the numbers
in
B7
and
C7
then it works fine. Is there any way to use the concantenate
function
without inserting the ' infront of a few thousand fields?
Changing
the
cell
format to text has not worked.
Thanks








--

Dave Peterson

Pa Maher
Concantenate numeric fields
 
Yes, it works fine now

"Dave Peterson" wrote:

Try
Tools|Options|Transition Tab|Uncheck "Transition formula evaluation"

If you're a plain old excel user, you may want to uncheck all those options.

If you've come from using Lotus 123 and like that behavior, then

=concatenate(Text(B7,"0"),Text(C7,"0"))
or just
=Text(B7,"0")&Text(C7,"0")

(use the number format that applies)

Pa Maher wrote:

XL 2003, example
B7 = 123456
B8 = 10
CONCATENTION works fine if I insert the ' in front of the number

"CLR" wrote:

The works fine for me in XL97...........what version are you
using, and what typical values do you have in B7 and C7?

Vaya con Dios,
Chuck, CABGx3


"Pa Maher" wrote in message
...
A report lists in 2 columns an account number and a sub-account number as
numeric fields.
I want to concantenate these fields. when I use "=CONCATENATE(B7,C7)" I
get
a #VALUE! error message. If I place a ' infront of the numbers in B7 and
C7
then it works fine. Is there any way to use the concantenate function
without inserting the ' infront of a few thousand fields? Changing the
cell
format to text has not worked.
Thanks




--

Dave Peterson


All times are GMT +1. The time now is 09:28 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com