display contents of preceeding cells consecutively

  • Thread starter Thread starter EngelseBoer
  • Start date Start date
E

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
 
If your data is in row 1, you can use this formula in F1:

=A1&B1&C1&D1&E1

Hope this helps,

Hutch
 
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
 
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
 
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
 
how can i force the concatenating to maintain them yet
still display an actual number ie not ""0002"
or ooo2 - but 0002
Click to expand...

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.
 
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
 
:) 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
 
That's not a numeric number, it's a *text* string.

I misinterpreted what you mean by "number".
 
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
 
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
 
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)
 
*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!
 
Back
Top