SOLVED

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%3CLINGO-SUB%20id%3D%22lingo-sub-2260509%22%20slang%3D%22en-US%22%3ERe%3A%20Alphanumeric%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2260509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1019863%22%20target%3D%22_blank%22%3E%40PaulSpanbauer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20need%20to%20use%20this%20VBA%20code%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20CommandButton1_Click()%0A%0AIf%20CommandButton1.Caption%20%3D%20%22Remove%20Leading%20Zero%22%20Then%0A%0ADim%20WorkRng%20As%20Range%0AOn%20Error%20Resume%20Next%0AxTitleId%20%3D%20%22Toggle%20Leading%20Zero%22%0A%0ASet%20WorkRng%20%3D%20Application.Selection%0ASet%20WorkRng%20%3D%20Application.InputBox(%22Select%20The%20Range%22%2C%20xTitleId%2C%20WorkRng.Address%2C%20Type%3A%3D8)%0A%0AWorkRng.NumberFormat%20%3D%20%22General%22%0AWorkRng.Value%20%3D%20WorkRng.Value%0A%0ACommandButton1.Caption%20%3D%20%22Add%20Leading%20Zero%22%0A%0AElseIf%20CommandButton1.Caption%20%3D%20%22Add%20Leading%20Zero%22%20Then%0A%0AxTitleId%20%3D%20%22Toggle%20Leading%20Zero%22%0A%0ASet%20WorkRng%20%3D%20Application.Selection%0ASet%20WorkRng%20%3D%20Application.InputBox(%22Select%20The%20Range%22%2C%20xTitleId%2C%20WorkRng.Address%2C%20Type%3A%3D8)%0A%0AWorkRng.NumberFormat%20%3D%20%22000000%22%0A%0ACommandButton1.Caption%20%3D%20%22Remove%20Leading%20Zero%22%0A%0AEnd%20If%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFrom%20Developer%20TAB%20hit%20Design%20Mode%20%26amp%3B%20on%20left%20is%20INSERT%2C%2C%20just%20hit%20it.%3C%2FLI%3E%3CLI%3EDraw%20Command%20button%20from%20ActiveX%20controls.%3C%2FLI%3E%3CLI%3ESelect%20%26amp%3B%20Right%20click%20the%20Command%20button%20%26amp%3B%20from%20menu%20find%20Properties.%3C%2FLI%3E%3CLI%3ESet%20the%20CAPTION%20as%20Remove%20Leading%20Zero.%3C%2FLI%3E%3CLI%3EPress%20ALT%2BF11%20to%20open%20VB%20editor.%3C%2FLI%3E%3CLI%3EThen%20Copy%20%26amp%3B%20Paste%20this%20code%20as%20Standard%20Module.%3C%2FLI%3E%3CLI%3ESave%20the%20Workbook%20as%20Macro%20Enabled%20*.xlsm.%3C%2FLI%3E%3CLI%3EFrom%20Developer%20TAB%2C%20release%20the%20Design%20Mode.%3C%2FLI%3E%3CLI%3ENow%20hit%20the%20command%20button%20%26amp%3B%20Follow%20the%20instructions.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2270575%22%20slang%3D%22en-US%22%3ERe%3A%20Alphanumeric%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20on%20a%20range%20of%20data.%26nbsp%3B%20What%20I%20want%20is%20a%20toggle%20button%20in%20Excel%20configuration%20that%20turns%20off%20the%20standard%20Excel%20functionality%20of%20removing%20leading%20zeros%20from%20alphanumeric%2C%20imported%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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.

14 Replies

@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.

@Rajesh-S 

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.

Leading ZEROs are an exceptionally used Cell format is not the default one also,,, so considering all that the VBA code I've suggested is best suitable and has liberty to work with the selected data range.

Using any programming method may spoil your data in another data range /Sheets,, and if you want to apply LEADING ZEROs as soon you open the Workbook,, in that case you need Workbook Open Event,, but remember then after you find Leading Zeros everywhere with all numeric Value !!
I do not need this for ranges already in Excel. I need it for importing data INTO Excel.

@PaulSpanbauer 

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.

 

 

PaulSpanbauer_0-1618403484802.png

I have to go into "Transform Data" and back out the "Changed Type" step.

 

PaulSpanbauer_1-1618403575707.pngPaulSpanbauer_2-1618403607151.png

^^ 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

There was something ... believe in cell formatting

Custom formatting

0; -0 ;; @ that was for the zeros in front of it

0. ### that was, I think, for after the decimal point
Try it

I tried the first "0; -0 ;; @" formula and works very well with zeros before the comma.

Hope you can be helped with it.

Niklino
Could you please write ,, how you are IMPORTING data ,,, any third party tool or from DATA tab ,,, Get External Data,,,,, and let me say,,, after importing the data with leading zeros can't you use the VBA code I've suggested,,, don't you think is a quite useful one !!

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.

@PaulSpanbauer 

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

@NikolinoDE 

 

PaulSpanbauer_0-1618501209873.pngPaulSpanbauer_1-1618501296070.png

^^ This is how and why I ended up here for my ask.

best response confirmed by PaulSpanbauer (Occasional Contributor)
Solution
I hope you don't misunderstand. It is good that you are here and of course you are welcome, as we are at microsoft in this forum.
I was concerned that the definition could be precise. As you described it at the beginning it was to be understood as if you needed a solution to your problem. But you would like to propose a solution / disseminate it. This could be better pointed out. Anyway, I would like to apologize from the start if I have worded anything wrong and accidentally offended you.

To put it briefly, please ask when and how much you want, there are no stupid questions, in the worst case you will get bad answers (some of them unintentionally from me).
Through questions I also learned what I know (or not) today.

I hope you continue to have fun with Excel .... the most beautiful invention since Schoko ... Uh, Microsoft.

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)
Thanks Nikolino - I do appreciate all the responses - and I wasn't aware of the full scope of this forum when I posed my initial query. Your points are well taken.

Cheers, Paul
If there is any way to better label or otherwise characterize this post as a 'product enhancement request' (or similar) please advise. It's a shame that the UserVoice forum seems to have been shut down. Thanks again.
Thank you for your understanding,
I wish you continued joy, health, love and success in your life.