Unable to see the comma separators in the address bar in excel

[thmurphy]

Hi - i have an excel sheet which has a list of documents and their associated page numbers separated by "," . Unfortunately, for any cells containing page numbers 100 or greater, i cannot seem to see the comma separator in the address bar , while i can see them in the cell itself . This is causing issues with me writing macros to split the page numbers .

Eg) Record 1 .. i am able to see the page index values separated by the commas in both the address bar and the cell

Batch_Id	New D#	Page_Indexs
753​	CA_359184_SP	3,4,5,6,7,8,9,10,11,12,13,14,15

Record 2 : Anything where the cell has page index 100 onwards , the cell shows the delimiter but the address bar does not . Please see the image

 

[Peter_SSs]

Try formatting column E as 'Text' before entering the page numbers. Excel thinks you are entering numbers with the comma as the thousands separator.

 

[thmurphy]

Hi Peter, Thanks for the revert.
Unfortunately, this output (with the page numbers) comes from a system generated report. Any other thoughts on how to get the comma separators to show in the address bar so i can use scripts to go ahead.

 

[thmurphy]

I even tried taking the values from my base excel and copying this into another excel column where i had preformatted one column as "Text" but no luck

 

[Peter_SSs]

this output (with the page numbers) comes from a system generated report.

How does the data get from the other system into an actual Excel workbook?
What are the step?

 

[thmurphy]

i believe it is exported into an .xls format

 

[Peter_SSs]

i believe it is exported into an .xls format

In that case I think the problem lies with the export from the other system. Is it possible to get the export from that system to use a different delimiter for the page indexes instead of a comma?

 

[thmurphy]

Thanks.. figured out a workaround.

1) Copy paste the data into a notepad
2) Copy the data back into an excel template where the column is set a "text" .. this resolves the issue.

 

[Peter_SSs]

1) Copy paste the data into a notepad

Copy from where?
Since you said the export was to xls format, I presumed that the commas had already disappeared (as you said you couldn't see them).