hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

=A1&B1&C1&D1&E1

--
__________________________________
HTH

Bob

"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

If your data is in row 1, you can use this formula in F1:

=A1&B1&C1&D1&E1

Hope this helps,

Hutch

"EngelseBoer" wrote:

hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be limited
to 15 digits. If the resulting string wll be longer than that then you'd
have to settle on the resulting string being *text* and remove the "--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

Ooops!

=--A1&B1&C1&D1&E1
That will return the result as a numeric number

The formula would be:

=--(A1&B1&C1&D1&E1)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited to 15 digits. If the resulting string wll be longer than that then
you'd have to settle on the resulting string being *text* and remove the
"--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be limited
to 15 digits. If the resulting string wll be longer than that then you'd
have to settle on the resulting string being *text* and remove the "--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

For each cell where you have used formatting to display leading zeros, change
the formula in F1 to use the TEXT function to put the leading zeros in front
of that cell value. For example, if A1 has the custom formatting, change the
formula in F1 like this:

=TEXT(A1,"0000")&B1&C1&D1&E1

Hope this helps,

Hutch

"EngelseBoer" wrote:

i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be limited
to 15 digits. If the resulting string wll be longer than that then you'd
have to settle on the resulting string being *text* and remove the "--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the result
as a *text string*. Excel *doesn't see* leading zeros in numeric values. The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited
to 15 digits. If the resulting string wll be longer than that then you'd
have to settle on the resulting string being *text* and remove the "--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

YES, it only worled on cell A soooo
I just had to modify it for my application
ie
=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

Thanks Tom

"Tom Hutchins" wrote:

For each cell where you have used formatting to display leading zeros, change
the formula in F1 to use the TEXT function to put the leading zeros in front
of that cell value. For example, if A1 has the custom formatting, change the
formula in F1 like this:

=TEXT(A1,"0000")&B1&C1&D1&E1

Hope this helps,

Hutch

"EngelseBoer" wrote:

i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be limited
to 15 digits. If the resulting string wll be longer than that then you'd
have to settle on the resulting string being *text* and remove the "--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

:) BUT I CAN
thanks to tom

=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

10 07 0178 0306 1376
= 1007017803061376

"T. Valko" wrote:

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the result
as a *text string*. Excel *doesn't see* leading zeros in numeric values. The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited
to 15 digits. If the resulting string wll be longer than that then you'd
have to settle on the resulting string being *text* and remove the "--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

That's not a numeric number, it's a *text* string.

I misinterpreted what you mean by "number".

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
:) BUT I CAN
thanks to tom

=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

10 07 0178 0306 1376
= 1007017803061376

"T. Valko" wrote:

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the
result
as a *text string*. Excel *doesn't see* leading zeros in numeric values.
The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited
to 15 digits. If the resulting string wll be longer than that then
you'd
have to settle on the resulting string being *text* and remove the
"--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

But that is not a number as Biff said, it is text that looks like a number.
If you force it to be a number, it will drop any leading 0 in A1

--
__________________________________
HTH

Bob

"EngelseBoer" wrote in message
...
:) BUT I CAN
thanks to tom

=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

10 07 0178 0306 1376
= 1007017803061376

"T. Valko" wrote:

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the
result
as a *text string*. Excel *doesn't see* leading zeros in numeric values.
The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited
to 15 digits. If the resulting string wll be longer than that then
you'd
have to settle on the resulting string being *text* and remove the
"--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

sry biff
you were right though i did frefer a number
but i can deal with text tough i have to copy and "paste value" elsewere
maybe i'll find out later that the wheels all fall off in my further
applications :)

thanks for the replies

"T. Valko" wrote:

That's not a numeric number, it's a *text* string.

I misinterpreted what you mean by "number".

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
:) BUT I CAN
thanks to tom

=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

10 07 0178 0306 1376
= 1007017803061376

"T. Valko" wrote:

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the
result
as a *text string*. Excel *doesn't see* leading zeros in numeric values.
The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited
to 15 digits. If the resulting string wll be longer than that then
you'd
have to settle on the resulting string being *text* and remove the
"--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

yes Biff is quite correct
as are you
the display is more important to me at this point
and i believe i can copy and "paste values" and be ok later on in the process
(i sure hope so)

"Bob Phillips" wrote:

But that is not a number as Biff said, it is text that looks like a number.
If you force it to be a number, it will drop any leading 0 in A1

--
__________________________________
HTH

Bob

"EngelseBoer" wrote in message
...
:) BUT I CAN
thanks to tom

=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

10 07 0178 0306 1376
= 1007017803061376

"T. Valko" wrote:

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the
result
as a *text string*. Excel *doesn't see* leading zeros in numeric values.
The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited
to 15 digits. If the resulting string wll be longer than that then
you'd
have to settle on the resulting string being *text* and remove the
"--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
hi

I would like to display contents of preceeding cells consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

Ok, thanks for the clarification!

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
sry biff
you were right though i did frefer a number
but i can deal with text tough i have to copy and "paste value" elsewere
maybe i'll find out later that the wheels all fall off in my further
applications :)

thanks for the replies

"T. Valko" wrote:

That's not a numeric number, it's a *text* string.

I misinterpreted what you mean by "number".

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
:) BUT I CAN
thanks to tom

=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

10 07 0178 0306 1376
= 1007017803061376

"T. Valko" wrote:

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the
result
as a *text string*. Excel *doesn't see* leading zeros in numeric
values.
The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING FOREVER
TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor
the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still display
an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would be
limited
to 15 digits. If the resulting string wll be longer than that then
you'd
have to settle on the resulting string being *text* and remove the
"--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in
message
...
hi

I would like to display contents of preceeding cells
consecutively
ie - generate a single number from numbers in 5 preceeding cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help

*BUT* ... when you say - "later on in the process"
You're *still* going to have a problem since you're working with 16 digits
.... a no-no with XL!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"EngelseBoer" wrote in message
...
yes Biff is quite correct
as are you
the display is more important to me at this point
and i believe i can copy and "paste values" and be ok later on in the
process
(i sure hope so)

"Bob Phillips" wrote:

But that is not a number as Biff said, it is text that looks like a
number.
If you force it to be a number, it will drop any leading 0 in A1

--
__________________________________
HTH

Bob

"EngelseBoer" wrote in message
...
:) BUT I CAN
thanks to tom


=TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"0000")&TEXT( D2,"0000")&TEXT(E2,"0000")

10 07 0178 0306 1376
= 1007017803061376

"T. Valko" wrote:

how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002

You can't. If you want leading zeros then you'll have to accept the
result
as a *text string*. Excel *doesn't see* leading zeros in numeric
values.
The
leading zeros are for *display purposes* only.


--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in
message
...
i HAVE TRIED THE VARIOUS MOTHODS AS WELL AS =CONCATENATE
AND ALL WORK
(I TRIED TO SAY I FOUND OUT HOW AFTER POSTING BUT WAS TAKING
FOREVER TO
DISPLAY MY MESSAGE HERE

OOPS CAPS

1 problem though -- i have foced come colums to display zeros befor
the
number
ie 2 = 0002 etc
all these methods drop the prceeding 000
how can i force the concatenating to maintain them yet still
display an
actual number ie not ""0002" or ooo2 - but 0002

"T. Valko" wrote:

Try this:

=--A1&B1&C1&D1&E1

That will return the result as a numeric number *but* this would
be
limited
to 15 digits. If the resulting string wll be longer than that then
you'd
have to settle on the resulting string being *text* and remove the
"--":

=A1&B1&C1&D1&E1

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in
message
...
hi

I would like to display contents of preceeding cells
consecutively
ie - generate a single number from numbers in 5 preceeding
cells

How might i be able to do this please

eg.

A B C D E = F
2 24 15 76 99 = 224157699

much obliged for any help