MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### formula to add a number to a long string **

• Follow

```I'm not quite sure how to best describe this:

I need a formula that will take the example below and return the "final
result"

Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578

Is there a way to do this?
```
 0

```This seems to work:
=LEFT(A1,LEN(A1)-3)&(RIGHT(TEXT(A1,"0"),3))+23

HTH,
Ryan---

--
Ryan---

"MMangen" wrote:

> I'm not quite sure how to best describe this:
>
> I need a formula that will take the example below and return the "final
> result"
>
> Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578
>
> Is there a way to do this?
```
 0

```=LEFT(A4,10)&--RIGHT(A4,3)+B4

Where 24.43.234.555 is in A4 and 23 is in B4.

HTH
Regards,
Howard

"MMangen" <MMangen@discussions.microsoft.com> wrote in message
news:47DC0797-C247-4D9B-81F8-878961028C19@microsoft.com...
> I'm not quite sure how to best describe this:
>
> I need a formula that will take the example below and return the "final
> result"
>
> Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578
>
> Is there a way to do this?

```
 0

```=TEXT(SUBSTITUTE(A1,".","")+B1,"00\.00\.000\.000")

"MMangen" wrote:

> I'm not quite sure how to best describe this:
>
> I need a formula that will take the example below and return the "final
> result"
>
> Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578
>
> Is there a way to do this?
```
 0

```You conditions are not well formed. Your first two numbers seem to indicate
that the other numbers do not have to be 3-digits long. So, if your Column A

12.345.67.89

and the Column B number was still 23, would the answer be 12.345.67.66 or
something else? If single digit final numbers are permitted, the if the

12.345.67.8

then what would the answer be?

--
Rick (MVP - Excel)

"MMangen" <MMangen@discussions.microsoft.com> wrote in message
news:47DC0797-C247-4D9B-81F8-878961028C19@microsoft.com...
> I'm not quite sure how to best describe this:
>
> I need a formula that will take the example below and return the "final
> result"
>
> Col. A = 24.43.234.555 and Col B= 23 making Final Result = 24.43.234.578
>
> Is there a way to do this?

```
 0

```According to Rick, my formula is

=3DLEFT(A1,FIND(" ",SUBSTITUTE(A1,".",REPT(" ",99),3)))&RIGHT(SUBSTITUTE
(A1,".",REPT(" ",99),3),99)+B1

under the assumption that there's 4 section in the text string (or 3
dotes)

On Nov 25, 8:37=A0am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> You conditions are not well formed. Your first two numbers seem to indica=
te
> that the other numbers do not have to be 3-digits long. So, if your Colum=
n A
>
> 12.345.67.89
>
> and the Column B number was still 23, would the answer be 12.345.67.66 or
> something else? If single digit final numbers are permitted, the if the
>
> 12.345.67.8
>
> then what would the answer be?
>
> --
> Rick (MVP - Excel)
>
> "MMangen" <MMan...@discussions.microsoft.com> wrote in message
>
> news:47DC0797-C247-4D9B-81F8-878961028C19@microsoft.com...
>
>
>
> > I'm not quite sure how to best describe this:
>
> > I need a formula that will take the example below and return the "final
> > result"
>
> > Col. A =3D 24.43.234.555 and Col B=3D 23 making Final Result =3D 24.43.=
234.578
>
> > Is there a way to do this?- Hide quoted text -
>
> - Show quoted text -

```
 0

5 Replies
259 Views

Similiar Articles:

7/16/2012 3:40:52 AM