concatenate

How can I concatenate 1919216111 to 919216.111

=1919216111 /1000

perhaps?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote in message
news:91F239BE-1484-4CB9-9D35-AD81AA074F64@microsoft.com...
> How can I concatenate 1919216111 to 919216.111

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


"Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote in message
news:91F239BE-1484-4CB9-9D35-AD81AA074F64@microsoft.com...
> How can I concatenate 1919216111 to 919216.111

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


"Tennisstar007" wrote:

> How can I concatenate 1919216111 to 919216.111

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


"Beege" <bwgilmanhah@comcast.net> wrote in message
news:440c9cb3_5@newsfeed.slurp.net...
> 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
>
>
> "Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote in message
> news:91F239BE-1484-4CB9-9D35-AD81AA074F64@microsoft.com...
>> How can I concatenate 1919216111 to 919216.111
>
>

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)

"Ron Coderre" wrote:

> 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
>
>
> "Tennisstar007" wrote:
>
> > How can I concatenate 1919216111 to 919216.111

Thanks!



"Bob Phillips" wrote:

> =1919216111 /1000
>
> perhaps?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote in message
> news:91F239BE-1484-4CB9-9D35-AD81AA074F64@microsoft.com...
> > How can I concatenate 1919216111 to 919216.111
>
>
>

Thanks for assisting me.



"Beege" wrote:

> Sorry, that first formula should have been B5&B6, because of the way you
> phrased the question...
>
>
> "Beege" <bwgilmanhah@comcast.net> wrote in message
> news:440c9cb3_5@newsfeed.slurp.net...
> > 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
> >
> >
> > "Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote in message
> > news:91F239BE-1484-4CB9-9D35-AD81AA074F64@microsoft.com...
> >> How can I concatenate 1919216111 to 919216.111
> >
> >
>
>
>

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)

"Ron Coderre" wrote:

> 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
>
>
> "Tennisstar007" wrote:
>
> > How can I concatenate 1919216111 to 919216.111

Thanks!



"Bob Phillips" wrote:

> =1919216111 /1000
>
> perhaps?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote in message
> news:91F239BE-1484-4CB9-9D35-AD81AA074F64@microsoft.com...
> > How can I concatenate 1919216111 to 919216.111
>
>
>

Thanks for assisting me.



"Beege" wrote:

> Sorry, that first formula should have been B5&B6, because of the way you
> phrased the question...
>
>
> "Beege" <bwgilmanhah@comcast.net> wrote in message
> news:440c9cb3_5@newsfeed.slurp.net...
> > 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
> >
> >
> > "Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote in message
> > news:91F239BE-1484-4CB9-9D35-AD81AA074F64@microsoft.com...
> >> How can I concatenate 1919216111 to 919216.111
> >
> >
>
>
>

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


"Tennisstar007" wrote:

> 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)
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "Tennisstar007" wrote:
> >
> > > How can I concatenate 1919216111 to 919216.111

A1: 919216.111
B1: 1919216111

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tennisstar007" <Tennisstar007@discussions.microsoft.com> wrote...
> How can I concatenate 1919216111 to 919216.111