CONCATENATE

B

Bob Phillips

=1919216111 /1000

perhaps?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Beege

Concatenate means join not add, right?

So, if 1919216111 is in cell B5 and 919216.111 is in B6, then the formula
is B5&B6. But if you mean change the value from one to the other, then
=(B5/1000)-1000000 would work

Beege
 
R

Ron Coderre

Not sure what exactly you're trying to do, so here are some guesses:

Starting with 1919216111 in A1

A text conversion might look like this:
=MID(A1*0.001,2,255)

A numeric conversion:
=--MID(A12*0.001,2,255)
or anothe way:
=MOD(A12,10^INT(LOG(A12)))*0.001

Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
B

Beege

Sorry, that first formula should have been B5&B6, because of the way you
phrased the question...
 
T

Tennisstar007

Thanks Ron! I am trying to convert a fund, department, FERC and budget
number into an account number format. Your formula worked! I just need to
add zero's to the end of two formatted account number. I tried to add the
zero by using the adding decimal button, but it does not work. Any
suggestions??? Thanks again, for your help.

Tennisstar007 (Pam)
 
R

Ron Coderre

Based on your post, I'm assuming you went with the text formula approach.
=MID(A1*0.001,2,255)

If that's true, then changing displayed decimal places will have no impact
on the "word" 919216.111

Try this:
=MID(A1*0.001,2,255)&"00"

Alternatively, changing displayed decimals will work on any of the numeric
methods.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
D

David McRitchie

A1: 919216.111
B1: 1919216111

=A1 & " " & B1
to concatenate as text with a space between
 
Top