How to take a value from on Cell and add a number

[Johnny Z]

Here is my delima.
In one column I have a bunch of IP addresses like 192.168.x.x. I want to
take the x.x numbers and add 9000 to it and put the result in a different
column. I hope there is some way to do this in excel. I am sorry I am a new
to excel and spent hrs searching the forum but did not find an answer.
Thanks a million for your help

 

[Myrna Larson]

Each of the 4 pieces of the IP address is a number between 0 and 256. What do
you mean when you say you want to add 9000 to the last 2 segments?


On Sun, 6 Feb 2005 18:19:01 -0800, "Johnny Z" <Johnny

 

[CLR]

Hi Johnny.........

You can use Data > Text to columns to separate the elements of your data
into individual columns, then you can do whatever math you wish to
them........and put them back together later if you wish with the
CONCATENATE function..........

hth
Vaya con Dios,
Chuck, CABGx3

 

[Max]

One way, using a formula ..

Assuming the data is in A2 down

192.168.1.1
192.168.2.2
192.168.1.2
192.168.2.3
192.168.1.3
etc

Put in B2:

=LEFT(A2,SEARCH(".",A2)-1)&"."&MID(A2,SEARCH(".",A2)+1,SEARCH(".",A2,SEARCH(".",A2)+1)-SEARCH(".",A2)-1)&"."&MID(A2,SEARCH(".",A2,SEARCH(".",A2)+1)+1,SEARCH(".",A2,SEARCH(".",A2,SEARCH(".",A2)+1)+1)-SEARCH(".",A2,SEARCH(".",A2)+1)-1)+9000&"."&MID(A2,SEARCH(".",A2,SEARCH(".",A2,SEARCH(".",A2)+1)+1)+1,99)+9000

Copy B2 down

For the sample data, the above will return:

192.168.9001.9001
192.168.9002.9002
192.168.9001.9002
192.168.9002.9003
192.168.9001.9003
etc

Another way would be to use Data > Text to Columns (delimited)
to split the data into 4 cols using the period: "." as the delimter

Then put 9000 in an empty cell, copy it,
select the 3rd and 4th cols and do a paste special > add

And concatenate the 4 split cols back into a 5th col

 

[Johnny Z]

I am sorry I should have been more clear. We are going through a MPLS/BGP
conversion and I need assign private BGP AS numbers to each of the nodes.

So my Plan was to take and address like 192.168.1.xxx and create an AS
number 95xxx. Let me give you 3 examples:

192.168.1.1 would get a AS number 95001

192.168.1.25 would get AS number 95025

192.168.1.115 would get AS number 95115

I hope this clarifies my question. Again thanks a lot for your help

 

[Johnny Z]

I am sorry I should have been more clear. We are going through a MPLS/BGP
conversion and I need assign private BGP AS numbers to each of the nodes.

So my Plan was to take and address like 192.168.1.xxx and create an AS
number 95xxx. Let me give you 3 examples:

192.168.1.1 would get a AS number 95001

192.168.1.25 would get AS number 95025

192.168.1.115 would get AS number 95115

I hope this clarifies my question. Again thanks a lot for your help

 

[Max]

One way, using a formula ..

Assuming the data is in A2 down

192.168.1.1
192.168.1.25
192.168.1.115
etc

Put in B2:
=95000+MID(A2,SEARCH(".",A2,SEARCH(".",A2,SEARCH(".",A2)+1)+1)+1,99)
Copy B2 down

This'll return:

95001
95025
95115
etc