Text to Columns not Working and Leading Zero Issue on Spreadsheet

A

alwayssunny

New Member
Joined
Dec 14, 2019
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Hey everyone, first off Happy Easter to those that celebrate it and great to see that this is the same helpful program. I am having a bit of an issue that I have spent over and hour trying to solve. First off the attached spreadsheet has an issue with zeros so any cell with less than 4 numbers means that zeros are missing. I went to format cells and then custom and entered 4 zeros it solved the issue only temporarily because as soon as I try to split the numbers into individual columns using Text to Columns it reverts.

When I use Text to Columns nothing simply happens. I have used the Xl2bb add-in to make it easier to explain my issues. Thanks for reading and looking forward to your help.

Register Tally.xlsx
ABC
1Detroit Midlands
2DateTimeItems
33/26/202104:55 PM984
43/26/202102:55 PM5,408
53/26/202112:55 PM1,204
63/26/202110:25 AM7,951
73/26/202108:25 AM4,945
83/25/202108:20 PM827
93/25/202104:55 PM7,471
103/25/202102:55 PM7,885
113/25/202112:55 PM8,634
123/25/202110:25 AM8,820
133/25/202108:25 AM8,640
143/24/202108:20 PM948
153/24/202104:55 PM8,787
163/24/202102:55 PM9,743
173/24/202112:55 PM3,674
183/24/202110:25 AM1,634
193/24/202108:25 AM6,705
203/23/202108:20 PM1,027
213/23/202104:55 PM6,392
223/23/202102:55 PM9,200
233/23/202112:55 PM3,126
243/23/202110:25 AM2,528
253/23/202108:25 AM7,969
263/22/202108:20 PM1,579
273/22/202104:55 PM2,222
283/22/202102:55 PM464
293/22/202112:55 PM2,691
303/22/202110:25 AM2,509
313/22/202108:25 AM5,107
323/21/202108:20 PM9,135
333/21/202104:55 PM845
343/21/202102:55 PM3,895
353/21/202112:55 PM3,691
363/21/202110:25 AM4,349
373/21/202108:25 AM6,585
383/20/202108:20 PM2,586
393/20/202104:55 PM8,399
403/20/202102:55 PM2,307
413/20/202112:55 PM5,756
423/20/202110:25 AM2,010
433/20/202108:25 AM1,495
443/19/202108:20 PM6,984
453/19/202104:55 PM394
463/19/202102:55 PM4,169
473/19/202112:55 PM5,234
483/19/202110:25 AM1,077
493/19/202108:25 AM3,251
503/18/202108:20 PM8,113
513/18/202104:55 PM462
523/18/202102:55 PM9,776
533/18/202112:55 PM790
543/18/202110:25 AM9,731
553/18/202108:25 AM5,084
563/17/202108:20 PM3,728
573/17/202104:55 PM3,254
583/17/202102:55 PM13
593/17/202112:55 PM4,228
603/17/202110:25 AM4,764
613/17/202108:25 AM7,211
623/16/202108:20 PM1,626
633/16/202104:55 PM8,194
643/16/202102:55 PM7,044
653/16/202112:55 PM8,072
663/16/202110:25 AM1,584
673/16/202108:25 AM6,055
683/15/202108:20 PM4,117
693/15/202104:55 PM2,484
703/15/202102:55 PM9,456
713/15/202112:55 PM7,698
723/15/202110:25 AM9,181
733/15/202108:25 AM7,545
743/14/202108:20 PM843
753/14/202104:55 PM9,913
763/14/202102:55 PM5,256
773/14/202112:55 PM311
783/14/202110:25 AM818
793/14/202108:25 AM7,447
803/13/202108:20 PM8,295
813/13/202104:55 PM1,751
823/13/202102:55 PM9,956
833/13/202112:55 PM8,051
843/13/202110:25 AM2,660
853/13/202108:25 AM9,923
863/12/202108:20 PM3,766
873/12/202104:55 PM5,656
883/12/202102:55 PM3,182
893/12/202112:55 PM686
903/12/202110:25 AM7,740
913/12/202108:25 AM2,351
923/11/202108:20 PM9,632
933/11/202104:55 PM15
943/11/202102:55 PM2,783
953/11/202112:55 PM2,907
963/11/202110:25 AM3,603
973/11/202108:25 AM5,969
983/10/202108:20 PM7,920
993/10/202104:55 PM9,929
1003/10/202102:55 PM5,997
1013/10/202112:55 PM4,889
1023/10/202110:25 AM1,246
Sheet1
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hi,

If you actually want "physical" leading zeros to make all your numbers in Column C 4 digits in length, you have to convert them to Text.
Custom formatting the Cell(s) only changes the display, Not the values, in the cells.
If you can explain what you want to achieve at the end, we can try to provide more help.
Otherwise, this is how you can change Column C values to 4 digits in length, it will become Text.
You can then Copy and Paste Values from Column D over Column C.

Book3.xlsx
ABCD
1Detroit Midlands
2DateTimeItems
33/26/20214:55 PM9840984
43/26/20212:55 PM54085408
53/26/202112:55 PM12041204
63/26/202110:25 AM79517951
73/26/20218:25 AM49454945
83/25/20218:20 PM8270827
93/25/20214:55 PM74717471
103/25/20212:55 PM78857885
113/25/202112:55 PM86348634
123/25/202110:25 AM88208820
133/25/20218:25 AM86408640
143/24/20218:20 PM9480948
153/24/20214:55 PM87878787
163/24/20212:55 PM97439743
173/24/202112:55 PM36743674
183/24/202110:25 AM16341634
193/24/20218:25 AM67056705
203/23/20218:20 PM10271027
213/23/20214:55 PM63926392
223/23/20212:55 PM92009200
233/23/202112:55 PM31263126
243/23/202110:25 AM25282528
253/23/20218:25 AM79697969
263/22/20218:20 PM15791579
273/22/20214:55 PM22222222
283/22/20212:55 PM4640464
293/22/202112:55 PM26912691
303/22/202110:25 AM25092509
313/22/20218:25 AM51075107
323/21/20218:20 PM91359135
333/21/20214:55 PM8450845
343/21/20212:55 PM38953895
353/21/202112:55 PM36913691
363/21/202110:25 AM43494349
373/21/20218:25 AM65856585
383/20/20218:20 PM25862586
393/20/20214:55 PM83998399
403/20/20212:55 PM23072307
413/20/202112:55 PM57565756
423/20/202110:25 AM20102010
433/20/20218:25 AM14951495
443/19/20218:20 PM69846984
453/19/20214:55 PM3940394
463/19/20212:55 PM41694169
473/19/202112:55 PM52345234
483/19/202110:25 AM10771077
493/19/20218:25 AM32513251
503/18/20218:20 PM81138113
513/18/20214:55 PM4620462
523/18/20212:55 PM97769776
533/18/202112:55 PM7900790
543/18/202110:25 AM97319731
553/18/20218:25 AM50845084
563/17/20218:20 PM37283728
573/17/20214:55 PM32543254
583/17/20212:55 PM130013
593/17/202112:55 PM42284228
603/17/202110:25 AM47644764
613/17/20218:25 AM72117211
623/16/20218:20 PM16261626
633/16/20214:55 PM81948194
643/16/20212:55 PM70447044
653/16/202112:55 PM80728072
663/16/202110:25 AM15841584
673/16/20218:25 AM60556055
683/15/20218:20 PM41174117
693/15/20214:55 PM24842484
703/15/20212:55 PM94569456
713/15/202112:55 PM76987698
723/15/202110:25 AM91819181
733/15/20218:25 AM75457545
743/14/20218:20 PM8430843
753/14/20214:55 PM99139913
763/14/20212:55 PM52565256
773/14/202112:55 PM3110311
783/14/202110:25 AM8180818
793/14/20218:25 AM74477447
803/13/20218:20 PM82958295
813/13/20214:55 PM17511751
823/13/20212:55 PM99569956
833/13/202112:55 PM80518051
843/13/202110:25 AM26602660
853/13/20218:25 AM99239923
863/12/20218:20 PM37663766
873/12/20214:55 PM56565656
883/12/20212:55 PM31823182
893/12/202112:55 PM6860686
903/12/202110:25 AM77407740
913/12/20218:25 AM23512351
923/11/20218:20 PM96329632
933/11/20214:55 PM150015
943/11/20212:55 PM27832783
953/11/202112:55 PM29072907
963/11/202110:25 AM36033603
973/11/20218:25 AM59695969
983/10/20218:20 PM79207920
993/10/20214:55 PM99299929
1003/10/20212:55 PM59975997
1013/10/202112:55 PM48894889
1023/10/202110:25 AM12461246
Sheet870
Cell Formulas
RangeFormula
D3:D102D3=TEXT(C3,"0000")
 
Upvote 0
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Solved
textjoin high and low of a sequence
Replies
8
Views
169
Peter_SSs
Peter_SSs
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw
Dazzawm
  • Solved
Macro To Find Duplicates And Put In Another Column
Replies
3
Views
98
Peter_SSs
Peter_SSs
S
  • Solved
Average in excel
Replies
3
Views
65
etaf
etaf
K
  • Question
VBA 20 minute time interval looping
Replies
1
Views
153
Kaper
Kaper

Forum statistics

Threads
1,214,979
Messages
6,122,551
Members
449,088
Latest member
davidcom

Share this page

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
Back
Top