IP address - thousands separator are displayed but excel thinks it is a normal number

Hi NBVC!

Thanks for replying that fast!

The cell formats:

For IPs that I need to change the format is "Number" with thousands separators turn on.
For IPs that I don't need to change the format is "Standard".

This already existed when I opened the xls the first time.

Thanks!

It sounds like Excel has tried to determine the best data type automatically when the IP addresses were loaded.

On the plus side all of the shorter IP addresses must be of type standard, otherwise they wouldn't appear correctly. For example, 95.57.1.68 as a number would be 9557168, which the thousands separator would parse as 9.557.168. This means that all of the ones of number type must be in the format (xx)x.xxx.xxx.xxx

Can you confirm that?

Hello

I'll quote your post

Because of this I can not split that kind of IP-addresses into different colums. Is there an opportunity to format these numbers into IP-addresses so the dots of the number would not just appear in the cell but also in the bar.

I think you should turn all your ip to texts then you can split them into different columns using TEXT to COLUMN found in the ribbon.

In that case, you can use this knowledge to create the following formulae to split to columns:

In B2 to B5, assuming IP address is in A2
=IF(ISTEXT(A2),LEFT(A2,SEARCH(".",A2)-1),LEFT(A2,LEN(A2)-9))

=IF(ISTEXT(A2),IF(MID(A2,LEN(B2)+3,1)=".",MID(A2,LEN(B2)+2,1),IF(MID(A2,LEN(B2)+4,1)=".",MID(A2,LEN(B2)+2,2),MID(A2,LEN(B2)+2,3))),RIGHT(LEFT(A2,LEN(A2)-6), 3))

=IF(ISTEXT(A2),IF(MID(A2,LEN(B2)+LEN(C2)+4,1)=".",MID(A2,LEN(B2)+LEN(C2)+3,1),IF(MID(A2,LEN(B2)*LEN(C2)+5,1)=".",MID(A2,LEN(B2)+LEN(C2)+3,2), MID(A2,LEN(B2)+LEN(C2)+3,3))),RIGHT(LEFT(A2,LEN(A2)-3),3))

=IF(ISTEXT(A2),IF(MID(A2,LEN(A2)-1,1)=".",RIGHT(A2,1),IF(MID(A2,LEN(A2)-2,1)=".",RIGHT(A2,2),RIGHT(A2,3))),RIGHT(A2,3))

OK, so if your current addresses are in column A, starting in row 1, then in cell B1 you can put the formula =IF(ISNUMBER(A1),SUBSTITUTE(TEXT(A1,"#,###"),",","."),A1) and copy that all of the way down.

That should put everything in the proper format in column B, you can now copy that column, use Paste Special|Values to overwrite that formula with the value returned and then delete column A. Job done.

Hi guys!

Sorry for answering that late, but I didn't have time earlier.

@Andrew-R: Your "formula looks great. But somehow it doesn't work. I am not really good in Excel, especially when it is dealt with formulas. ..Could the problem be that I am using a German version of Windows 7? My Office is English but does support German too. I know the problem of "," and ".".

I attached a screenshot of the formula. Should I use a semicolon instead of the comma?

@ martindwilson: I got the Excel sheet from the Google Dart Team in Ireland. Is there anything I can try to surpass the problem you described? In Excel my decimal separator is "," and my thousands separator is "." It is set to use the system settings but when I am right then these are the right separators for my area. Should I exchange them?

Greetings guys!

All the best!
Johannes

Yes, *some* of the commas need to be semi-colons, and one of the things you've entered as a comma is actually a full-stop. The formula should be =IF(ISNUMBER(A1);SUBSTITUTE(TEXT(A1;"#,###");",";".");A1)

Hi Andrew-R!

Thanks for your answer. But when I use the code then the numbers are pure text without the dots in between. ..But I need the dots in between.

Greetings and thanks

John,

If it's not putting the dots in then it's not recognising the field as numbers. You could try changing my fomula to: =IF(NOT(ISERROR(VALUE(A1)));SUBSTITUTE(TEXT(VALUE(A1);"#,###");",";".");A1)

Hi john_kaufi,

thanks for starting this thread, certainly helped me a lot!

I encountered the same problem and I followed the suggestion of martindwilson below.

then correct separator is , for 1000 . for decimal
i think you need to decide what is the priority for your regional settings!

The problem is with regional setting, I think because you use "." for both decimal and thousand separator. Once I changed my decimal setting to "." and my thousand separator to "," the problem is solved.

Thanks again guys!

Max

I had the same problem but I find an easy fix. Just copy all of your IPs on a notepad >> Open an excel sheet and select for example Column A >> Edit format and select all to "Text" >> Copy all the IPs from your Notepad >> Go back to your spreadsheet, select the column A again, right click, paste special and select text again.

It will paste all your IPs keeping the format with the dots (.) then you can use text in columns to separate every block of #s

Hope this is helpful!

Originally Posted by Andrew-R

OK, so if your current addresses are in column A, starting in row 1, then in cell B1 you can put the formula =IF(ISNUMBER(A1),SUBSTITUTE(TEXT(A1,"#,###"),",","."),A1) and copy that all of the way down.

That should put everything in the proper format in column B, you can now copy that column, use Paste Special|Values to overwrite that formula with the value returned and then delete column A. Job done.

I registered just to say thank you for this post. Had a list of over a million IPs that were full of commas, and you just made my life easier. Internet kuddos, big time

Originally Posted by martindwilson

i work with hundreds of spread sheets with ip addresses on and never have that problem
it could happen if your thousand separator is set as full stop instead of comma in regional settings tho..

Thank you, thank you, thank you I have worked for days,asking colleagues and ChatGPT and i got nowhere with formating in Excell - THIS was my issue, the dot instead of the comma in regional settings. Thank you! ! !

JanV