concatenation and number format

  • Thread starter Thread starter goss
  • Start date Start date
G

goss

Hi all.

Using xl xp pro

I have a list of numbers in this format 7001.1200 or 7001.1201
In col B I need a formula that leaves the 8 digit number w/o the
period separator.

I tried concatenate(Right(A1,4),(LeftA1,4))
But did not handle the trailing zeroes
So on 7001.1200 I received 70011.12 instead of 70011200

Any ideas
 
=SUBSTITUTE(A1,".","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Bob

Here's what I'm using:
=VALUE((SUBSTITUTE(A133,".","")))

Returns:
700112

I tried cusotm format 00000000

Returns:
00700112

What do I need to do to force trailing zeroes
 
How about:

=a133*10000

goss < said:
Thanks Bob

Here's what I'm using:
=VALUE((SUBSTITUTE(A133,".","")))

Returns:
700112

I tried cusotm format 00000000

Returns:
00700112

What do I need to do to force trailing zeroes?
Click to expand...
 
Thanks Bob.

My bad.
Sometimes the number may be something like
7001.9999
So formula will result in 70019999.

Other times
7001.9990 or 7001.9900
will result in 7001999 or 700199 respectively

But I need all results regarldless of input to be
eight digit with trailing zeroes not leading
 
Hi all.

Using xl xp pro

I have a list of numbers in this format 7001.1200 or 7001.1201
In col B I need a formula that leaves the 8 digit number w/o the
period separator.

I tried concatenate(Right(A1,4),(LeftA1,4))
But did not handle the trailing zeroes
So on 7001.1200 I received 70011.12 instead of 70011200

Any ideas?
Click to expand...


=TEXT(A1*10^4,"00000000")


--ron
 
Back
Top