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

thmurphy

New Member
Joined
Nov 19, 2009
Messages
7
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_IdNew D#Page_Indexs
753​
CA_359184_SP3,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
 

Attachments

  • Problem.jpg
    Problem.jpg
    169.6 KB · Views: 7

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Try formatting column E as 'Text' before entering the page numbers. Excel thinks you are entering numbers with the comma as the thousands separator.

1605265738939.png
 

thmurphy

New Member
Joined
Nov 19, 2009
Messages
7
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

New Member
Joined
Nov 19, 2009
Messages
7
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 19, 2009
Messages
7
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,721
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top