How to Cut String by Section

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

Try: left(Address,index(Address,'.',2)-1)

[#3]

Thank you very much. Greatly appreciate.

[#4]

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

[#5]

try subField(Address,'.',1) &  '.' & subField(Address,'.',2)

[#6]

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!

[#7]

if(index(Address,'.')=0,Address,subField(Address,'.',1) &  '.' & subField(Address,'.',2))

[#8]

the above should help

[#9]

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