display contents of preceeding cells consecutively

[EngelseBoer]

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

 

[Bob Phillips]

=A1&B1&C1&D1&E1

 

[Tom Hutchins]

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

=A1&B1&C1&D1&E1

Hope this helps,

Hutch

 

[T. Valko]

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

 

[T. Valko]

Ooops!

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

The formula would be:

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

 

[EngelseBoer]

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

 

[Tom Hutchins]

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

 

[T. Valko]

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.

 

[EngelseBoer]

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

 

[EngelseBoer]

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]

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

I misinterpreted what you mean by "number".

 

[Bob Phillips]

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

 

[EngelseBoer]

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

 

[EngelseBoer]

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)

 

[T. Valko]

Ok, thanks for the clarification!

 

[Ragdyer]

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