Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everybody,
I have a set of IP address such as:
IP | Address |
---|---|
IP1 | 255.01.01.01 |
IP2 | 255.120.01.0 |
IP3 | 155.10.01.02 |
IP4 | 55.02.03.123 |
I want to get data like this:
IP | Address |
---|---|
IP1 | 225.01 |
IP2 | 255.120 |
IP3 | 155.10 |
IP4 | 55.02 |
Thanks in advance,
Dust
Try: left(Address,index(Address,'.',2)-1)
Try: left(Address,index(Address,'.',2)-1)
Thank you very much. Greatly appreciate.
Hi Gysbert,
Thank you very much for your help. Real data is more complex than we can think.
I just find some data only go to level 2
IP | Address |
---|---|
IP1 | 255.01.01.01 |
IP2 | 255.120.01.0 |
IP3 | 155.10.01.02 |
IP4 | 55.02 |
So I need to add a '.' when load this data. Otherwise IP4 will not load.
I tried to load like this:
Load IP&'.' as IP FROM DB
Do you have any suggestion?
Thank you very much,
Dust
try subField(Address,'.',1) & '.' & subField(Address,'.',2)
Thank you V4!
Your script works but when Address only have level 1 it will add '.' after.
IP | Address |
---|---|
IP1 | 255.01.01.01 |
IP2 | 255.120.01.0 |
IP3 | 155 |
IP4 | 55.02 |
to
IP | Address |
---|---|
IP1 | 255.01 |
IP2 | 255.120 |
IP3 | 155. |
IP4 | 55.02 |
IP3 will followed by '.'
Thank you!
if(index(Address,'.')=0,Address,subField(Address,'.',1) & '.' & subField(Address,'.',2))
the above should help
Thank you very much --V4. I appreciate your solution. Do you think we can add a '.' to the string first? Then cut string by '.'?
Best regards,
Dust