Apr 07 2021 02:01 PM
Hi Team Excel
I would like to see added a toggle switch to turn on or off stripping leading zero's from numeric text data when opened or imported into Excel. Excel automatically strips leading zero's. I have to use power query to retrieve data (for example, from comma delimited file) and manually back out the automatic formatting from the query.
Sometimes I want to retain leading zero's, sometimes I do not. A toggle switch would help me control the Excel behavior. Thanks for reading.
Apr 07 2021 11:36 PM - edited Apr 07 2021 11:40 PM
You need to use this VBA code:
Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Remove Leading Zero" Then
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Toggle Leading Zero"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select The Range", xTitleId, WorkRng.Address, Type:=8)
WorkRng.NumberFormat = "General"
WorkRng.Value = WorkRng.Value
CommandButton1.Caption = "Add Leading Zero"
ElseIf CommandButton1.Caption = "Add Leading Zero" Then
xTitleId = "Toggle Leading Zero"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select The Range", xTitleId, WorkRng.Address, Type:=8)
WorkRng.NumberFormat = "000000"
CommandButton1.Caption = "Remove Leading Zero"
End If
How it works:
Apr 13 2021 10:12 AM
This works on a range of data. What I want is a toggle button in Excel configuration that turns off the standard Excel functionality of removing leading zeros from alphanumeric, imported data.
Apr 14 2021 12:17 AM
Apr 14 2021 05:02 AM
Apr 14 2021 05:35 AM
Consider example source data:
Segment 1 Segment2 Segment3 Amount
0000 0001 0002 10.00
0001 0100 0025 25.00
1000 0005 2500 10.00
3000 0000 0050 5.00
I want to import this data with the leading zero's in the segment columns intact.
Right now I have to use power query to import; then back out the automatic formatting step.
I have to go into "Transform Data" and back out the "Changed Type" step.
^^ Now I have the correct data.
What I want is a toggle switch in Excel that disables the automatic trimming of leading zero's from imported data.
I hope this makes sense.
Thanks, Paul Spanbauer
Apr 14 2021 11:12 AM
Apr 14 2021 10:31 PM
Apr 15 2021 06:33 AM - edited Apr 15 2021 06:35 AM
How I import? Data tab on the ribbon --> Get Data --> From Text/CSV.
@Rajesh, @NikolinoDE your suggestions are fine as workarounds. What I am asking for is the ability to import the data with no workarounds. I want the imported data to be exactly as it appeared in the source without having to run a macro or format the cells.
This is the forum to suggest new functionality to the Microsoft development team is it not?
Thanks for the suggestions - but I can handle workarounds already. I want to import the data without workarounds.
Apr 15 2021 08:35 AM
If you have or would like to have a suggested solution,
you can put it in this area " https://excel.uservoice.com/ " as a suggestion.
We are just like you here, simple users offering help to other users.
Simple people who offer their help "selflessly" (as far as one could say this).
Please give your feedback there, so we can spend our time on the people who really need it.
Thank you for your understanding and time.
Nikolino
Apr 15 2021 08:42 AM
Apr 15 2021 11:45 AM
SolutionApr 15 2021 11:50 AM
Apr 15 2021 11:53 AM
Apr 15 2021 11:54 AM
Apr 15 2021 11:45 AM
Solution