Alphanumeric Data

%3CLINGO-SUB%20id%3D%22lingo-sub-2259836%22%20slang%3D%22en-US%22%3EAlphanumeric%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2259836%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20see%20added%20a%20toggle%20switch%20to%20turn%20on%20or%20off%20stripping%20leading%20zero's%20from%20numeric%20text%20data%20when%20opened%20or%20imported%20into%20Excel.%26nbsp%3B%20Excel%20automatically%20strips%20leading%20zero's.%26nbsp%3B%20I%20have%20to%20use%20power%20query%20to%20retrieve%20data%20(for%20example%2C%20from%20comma%20delimited%20file)%20and%20manually%20back%20out%20the%20automatic%20formatting%20from%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESometimes%20I%20want%20to%20retain%20leading%20zero's%2C%20sometimes%20I%20do%20not.%26nbsp%3B%20A%20toggle%20switch%20would%20help%20me%20control%20the%20Excel%20behavior.%26nbsp%3B%20Thanks%20for%20reading.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2259836%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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.

1 Reply

@PaulSpanbauer 

 

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:

  • From Developer TAB hit Design Mode & on left is INSERT,, just hit it.
  • Draw Command button from ActiveX controls.
  • Select & Right click the Command button & from menu find Properties.
  • Set the CAPTION as Remove Leading Zero.
  • Press ALT+F11 to open VB editor.
  • Then Copy & Paste this code as Standard Module.
  • Save the Workbook as Macro Enabled *.xlsm.
  • From Developer TAB, release the Design Mode.
  • Now hit the command button & Follow the instructions.