Increment count on text field

  • Thread starter Thread starter ljhillman
  • Start date Start date
L

ljhillman

I have two fields that I need to increment. The first is an IP addres
field as text formatted as " 10.213.130.100 ". I need to add one t
the last octet so the next field below it is " 10.213.130.101 " and on
The other field I need to increment is also a text field that contain
4 characters and 3 numbers - " ABCD100 and it needs to be incremente
down the sheet as ABCD101, ABCD102...

I am new to doing formulas in Excel and need some help. Thanks
 
A formula for the IP addresses is:

=IF(RIGHT(I10,3)+1<100,IF(RIGHT(I10,3)+1<10,LEFT(I10,13)&RIGHT(I10,3)+1,LEFT(I10,12)&RIGHT(I10,3)+1),LEFT(I10,11)&RIGHT(I10,3)+1)

For the ABCD100, use:

=IF(RIGHT(I13,3)+1<100,IF(RIGHT(I13,3)+1<10,LEFT(I13,6)&RIGHT(I10,3)+1,LEFT(I13,5)&RIGHT(I13,3)+1),LEFT(I13,4)&RIGHT(I13,3)+1)

In any case, it would be easier to just grab the initial cell by th
lower right hand corner and drag it. Excel should automaticall
increment.
 
One quick and easy way is to enter " 10.213.130.100 " in a cell without the quotes. Then just fill down, i.e. drag the little black square in the bottom right corner of the cell downwards. Excel will automatically increment the cell value, even though is text. Same goes for " ABCD101 ".

HTH
Anders Silven
 
Hi

Manually:
Enter into some cell the value 10.213.130.100 or ABCD100
Into cell below of it enter the value 10.213.130.101 or ABCD101
Select both cells, and drag down to fill series

The formula
Enter into some cell the formula
="10.213.130."&(100+ROW(A1)-ROW(A$1))
or
="ABCD"&(100+ROW(A1)-ROW(A$1))
and copy down.
 
Thanks for the replies. For the second question (ABCD100) using th
lower right corner worked great...forgot about that one.

For the first question - I need to be more detailed. I am onl
incrementing one address, one time. The next line has a different se
of numbers for an address, and the format of the sheet has the cells o
the same row. So in row 8, column B is the address 10.213.130.100 an
I need 10.213.130.101 in column C for example and then on row 9, th
address is 10.217.150.62 in column B and I need 10.217.150.63 in colum
C and so on. Not as simple, but doable
 
Try this; with this the length of the last section of the IP doesn't matter,
it looks for the placement of the "."


=LEFT(A1,(FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)))&RIGHT(A1,LEN(A1)-(FIN
D(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)))+1
 
With this he'd never be able to go from 10.215.120.79 to 10.215.120.80, he'd
get 10.215.120.710




In C8 enter

=LEFT(B8,LEN(B8)-1)&RIGHT(B8,1)+1

fill down, the right

HTH
Anders Silven
 
You are quite right Dave. It seemed a bit to easy compared to your formula but at the late hour I let it pass through. Thanks. Strike my post :(

Best regards,
Anders Silven


Dave R. said:
With this he'd never be able to go from 10.215.120.79 to 10.215.120.80, he'd
get 10.215.120.710




In C8 enter

=LEFT(B8,LEN(B8)-1)&RIGHT(B8,1)+1

fill down, the right

HTH
Anders Silven
Click to expand...
 
Hi

Enter start IP's into column B
Into cell C8 enter the formula
=IF(MID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8
,3)))+1,3)*1+COLUMN(C8)-COLUMN($B8)>255,"",LEFT($B8,LEN($B8)-LEN(MID(RIGHT($
B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".";RIGHT($B8,3)))+1,3)))&(M
ID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".";RIGHT($B8,3)))
+1,3)*1+COLUMN(C8)-COLUMN($B8)))
and copy it over the range you want incremented addresses to appear (I
included a check for IP-value 255, so no non-valid IP's are returned).

Duh! A big one! Let's make it more accetable!

Select cell C8 (I'm not sure is it needed at all, but I never did finnd time
to find it out, and better to be on safe side) and define named ranges
IP=MID(RIGHT(Sheet1!$B8,3),IF(ISERROR(FIND(".",RIGHT(Sheet1!$B8,3))),0,FIND(
".",RIGHT(Sheet1!$B8,3)))+1,3)*1+COLUMN(Sheet1!C8)-COLUMN(Sheet1!$B8)
Base=LEFT(Sheet1!$B8,LEN(Sheet1!$B8)-LEN(MID(RIGHT(Sheet1!$B8,3),IF(ISERROR(
FIND(".",RIGHT(Sheet1!$B8,3))),0,FIND(".",RIGHT(Sheet1!$B8,3)))+1,3)))

Now you can have in C8 the formula
=IF(IP>255;"";Base&(IP))
(It's nice now, is it? And more understandable too!) and copy to range for
incremented addresses.
 
In first formula a couple of semicolons are not replaced with commas, so
there must be

=IF(MID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8
,3)))+1,3)*1+COLUMN(C8)-COLUMN($B8)>255,"",LEFT($B8,LEN($B8)-LEN(MID(RIGHT($
B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8,3)))+1,3)))&(M
ID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8,3)))
+1,3)*1+COLUMN(C8)-COLUMN($B8)))

I hope it's right now.
Arvi Laanemets
 
Arvi Laanemets said:
In first formula a couple of semicolons are not replaced with commas, so
there must be

=IF(MID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8
,3)))+1,3)*1+COLUMN(C8)-COLUMN($B8)>255,"",LEFT($B8,LEN($B8)-LEN(MID(RIGHT($
B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8,3)))+1,3)))&(M
ID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8,3)))
+1,3)*1+COLUMN(C8)-COLUMN($B8)))

I hope it's right now.
Click to expand...



Good god, I hope so too! :)
 
How does this check out Arvi?
Just a little shorter.

=IF(INT(RIGHT(A8,3))>0,LEFT(A8,LEN(A8)-3)&RIGHT(A8,3)+1,LEFT(A8,LEN(A8)-2)&R
IGHT(A8,2)+1)
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


In first formula a couple of semicolons are not replaced with commas, so
there must be

=IF(MID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8
,3)))+1,3)*1+COLUMN(C8)-COLUMN($B8)>255,"",LEFT($B8,LEN($B8)-LEN(MID(RIGHT($
B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8,3)))+1,3)))&(M
ID(RIGHT($B8,3),IF(ISERROR(FIND(".",RIGHT($B8,3))),0,FIND(".",RIGHT($B8,3)))
+1,3)*1+COLUMN(C8)-COLUMN($B8)))

I hope it's right now.
Arvi Laanemets
 
How does this check out Arvi?
Just a little shorter.

=IF(INT(RIGHT(A8,3))>0,LEFT(A8,LEN(A8)-3)&RIGHT(A8,3)+1,LEFT(A8,LEN(A8)-2)&
RIGHT(A8,2)+1)
Click to expand...

Depends on how many assumptions you get to make about the range of IP addresses.
With 1.2.3.4 in A8, the formula above returns 1.2.4.4 rather than 1.2.3.5.

There's also the question of what should happen when the low order octet is
initially 255. That is, 123.255.255.255 + 1 should result in 124.0.0.0 to be as
safe as possible. As a practical matter, I doubt there's ever any real world
need for carry between octets. In which case, maybe

=LEFT(A20,LEN(A20)-MATCH(".",LEFT(RIGHT(A20,{2,3,4}),1),0))
&(RIGHT(A20,MATCH(".",LEFT(RIGHT(A20,{2,3,4}),1),0))+1)
 
Hi

As Harlan pointed to, last 3 characters of starting IP can be ###, .## or
#.#
So what returns your formula p.e. with starting IP = 192.168.0.1 ?
Or with styarting IP = 192.168.0.255 ?

I finished with so monstrous of formula, because I had:
1. to check out the presence of period in last 3 characters, and to extract
only the last number in address
2. to cope wit case when last 3 numbers didn't contain any period
3. to avoid IP exceeding 255 - when this is case, my formula returns an
empty string.
 
Harlan and you are much wiser then I, because I never *realized* that an IP
address could contain a single digit.

I knew the term "octet", so I figured 8digits, minimum two each.

I am good at dyeing though.<g>
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi

As Harlan pointed to, last 3 characters of starting IP can be ###, .## or
#.#
So what returns your formula p.e. with starting IP = 192.168.0.1 ?
Or with styarting IP = 192.168.0.255 ?

I finished with so monstrous of formula, because I had:
1. to check out the presence of period in last 3 characters, and to extract
only the last number in address
2. to cope wit case when last 3 numbers didn't contain any period
3. to avoid IP exceeding 255 - when this is case, my formula returns an
empty string.
 
Back
Top