Excel?? easy one for someone (not me) lol 1

[octelvoice]

I have a large spreadsheet, and I need to find and replace several IP addresses in this sheet i.e. 10.x.x.x and the x= a numeric value. I have tried the *, and the ? and the ~ but all I end up finding is the 10.x and when I try and replace it I end up replacing the 10.x so I end up with 10.x.xxx.xxx.xxx.xxx.xxx.xxx instead of the 10.x.x.x I'm looking for.
I'm sure there's an easy solution, I just don't know what it is.

 

[stduc]

Not sure what your trying to achieve here

If you're - say - trying to change all cells with 10.1.2.3 to 10.10.20.25 then a simple find & replace will do.

If you are trying to replace all cells that start 10.1 with 10.2 say - so that 10.1.2.3 become 10.2.2.3 and 10.1.5.6 becomes 10.2.5.6 then you will need a formulae like this.

=IF(LEFT(A1,3)="10.1","10.2" & MID(A1,5,99),A1)

which will copy the contents on no match & change on a match - you can than copy the results and past as values to another sheet. Or paste back in place & delete the original column.

Hope this answers your question.

 

[octelvoice]

That's what I'm trying to do.... change 10.1.2.3 and then 10.4.5.6 and 10.7.8.9 to all be "10.x.x.x "

I'm useing a spreadsheet that has IP's from another site, and don't have the new IP's for this site, so I need to change all the specific 10.1.2.3 addresses to a generic "10.x.x.x" until I receive the new ones.

Does this formula you provide alow for that kind fo change?

Thanks

 

[stduc]

If all your trying to do is replace specific IP address's with 10.x.x.x I fail to see why you are failing using find & replace.

What's wrong with multiple find/replaces starting with 10.1.2.3 to 10.x.x.x ?

My formula was an example - look up the syntax of the IF formula - but basically it is.

If (condition , if true, if false)

You can nest them like this

If (condition , If (condition , if true, if false), if false)

But I suspect I'm just confusing you now!

 

[octelvoice]

would I use that formula in the find area?

the issue is that this spreadsheet has many many diffrent IP's I'm sure they all start with 10. but the next three octets are all diffrent. if I use a find 10. and replace with 10.x.x.x what I get is everywhere you have a 10.1.2.3 you now have a 10.x.x.x.1.2.3

so if I setup the formula you gave as an example to match my search do you think that will get me to where I want to be?

OH you might know... if I have a spreadsheet where everyother line is blank and I want to keep the data as it is right now, only remove all the extra blank lines? how might you go about that? I have tried to find and replace with a blank but that doesn't do it?

Thanks

 

[sbudzynski]

i would sort that particular column so that all the 10.(whatevers) are in order. Then highlight all of them and paste 10.x.x.x

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[sbudzynski]

the sort function would also correct your second issue, unless of course you prefer to have your sheet sorted in no particular order..

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[sbudzynski]

you could actually maintain whatever order your sheet is now by creating a new column [line number] and then inserting a number for each row starting at 1 then skip the blank 2 then skip the blank 3 and then drag that down through your spreadsheet. you could then sort by that column which would knock out all the extra black rows, and then you could delete the column [line number].

but if your spreadsheet is already in some particular order then just sort the whole sheet by that order and it will put all the blank rows at the end of the sheet, much much faster..

i hope this solves your problem, if not I clearly do not understand what your problem is or what the heck im talking about

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[octelvoice]

I don't think so I have tried that and it changes the order of the data, I need to keep the data exactly as it is now, and just remove the blank lines.
Can't sort the on the 10. because their all mixed inside of test.
keep the idea's comming... maybe I haven't explained the issue well enough? I have clipped a small sample of the sheet I'm working with so you can see what I mean.

I know it looks jummbled here but all the below came from colum A,B,&C of my sheet.... that I need to change the IP's in.

Thanks



Apply interface specific script to Odd MDF switch: 30 mins
"On NMALC2CDRS-0101 (10.58.126.3)
! Allow X-Rack Vlan interface, to accept incoming DCHP broadcasts
interface Vlan196
ip directed-broadcast
!
! Shutdown Legacy IDF #1 VLans
interface Vlan200
shut
!
interface Vlan201
shut
!"
"Convert MDF to IDF uplink from TRUNK to L3 Routed
default interface GigabitEthernet3/1
!
interface GigabitEthernet3/1
description *** IDF #1 Switch (Gi7/1) ***
ip address 10.111.61.1 255.255.255.252
ip pim sparse-mode
ip ospf network point-to-point
service-policy output QOS
mls qos trust ip-precedence
no shut
!"
"Add routing for the new 10.111.0.0 Network
router ospf 300
area 10.111.0.0 nssa no summary
area 10.111.0.0 range 10.111.0.0 255.255.192.0
no passive-interface GigabitEthernet3/1
network 10.111.0.0 0.0.63.255 area 10.111.0.0
!"
"Test Connectivity to IDF
ping 10.111.62.11
ping 10.111.8.1
ping 10.111.9.1
ping 10.111.12.1
ping 10.111.13.1"
Test ability to pull DHCP – Attach a laptop and ensure you can pull DHCP 10 mins

 

[octelvoice]

That's good about the blanks!!! I think that will work!!!
Thanks

 

[sbudzynski]

try this
find 10.1*
replace 10.(whatever)

see if that works

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"

 

[octelvoice]

That did IT!!! yeah!!! thank you

 

[sbudzynski]

Anytime

----------

Steve Budzynski


"So, pass another round around for the kids. Who have nothing left to lose and for those souls old and sold out by the soles of my shoes"