excel - Using wildcards in VBA cells.replace -

excel - Using wildcards in VBA cells.replace -

i'm looking write function in excel add together leading zeroes octets create ip address: e.g in 172.19.1.17 want to alter .19. .019., .1. .001., , .17 @ end .017.

te cells.teplace function not seem take ? wildcard. also, there way can represent 'end of string' i'll able add together leading zeroes lastly octet, .17 in illustration above.

thanks ian

cells.replace what:=".1?.", replacement:=".01?.", lookat:=xlpart, _ searchorder:=xlbyrows, matchcase:=false, searchformat:=false, _ replaceformat:=false

this find "10." "11." "12." etc. replaces them ".01?."

as alternative may utilize formula add together zeros ip parts (it looks terrible, treats separately parts , mix them up):

=rept(0,4-find(".",a1))&left(a1,find(".",a1)-1)&"."& rept(0,4-find("@",substitute(a1,".","@",2))+find(".",a1))&mid(a1,find(".",a1)+1,find("@",substitute(a1,".","@",2))-find(".",a1)-1)&"."& rept(0,4-find("@",substitute(a1,".","@",3))+find("@",substitute(a1,".","@",2)))&mid(a1,find("@",substitute(a1,".","@",2))+1,find("@",substitute(a1,".","@",3))-find("@",substitute(a1,".","@",2))-1)&"."& rept(0,3-len(a1)+find("@",substitute(a1,".","@",3)))&right(a1,len(a1)-find("@",substitute(a1,".","@",3)))

you may paste b1 (assuming ips in column starting a1) regardless line breaks.

sample file: https://www.dropbox.com/s/vun6urvukch9uvv/ipoctets.xlsx

excel ip-address