09-23-2019 01:55 PM
09-23-2019 01:55 PM
I have a file that I use for analysis on a monthly basis, and I was wondering if is there a way to format a column to always be G0000xxxx. The column is called Grant and many people instead of entering the entire grant code, they enter G 1234, I want to add a script of formatting so that if anyone enters G 1234 it will automatically change to G00001234.
09-23-2019 02:17 PM
You may try something like this...
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim Matches As Object Dim str As String If Target.Column = 1 And Target.Row > 1 Then On Error GoTo Skip Application.EnableEvents = False If Target <> "" Then str = Replace(Target.Value, " ", "") With CreateObject("VBScript.RegExp") .Global = False .IgnoreCase = True .Pattern = "(G)(\d+)" If .Test(str) Then Set Matches = .Execute(str) str = UCase(Matches(0).SubMatches(0)) & Format(Matches(0).SubMatches(1), "00000000") Target = str End If End With End If End If Skip: Application.EnableEvents = True End Sub
The code above is for Sheet Change Event and is placed on Sheet1 Module. To view the code, right click on Sheet1 Tab and choose View Code.
As per the code, if you input the Grant Code in Column A starting from Row#2, the code will be converted into the desired format.
09-24-2019 01:48 PM - edited 09-24-2019 02:04 PM
Hello @amandaramos ,
an alternative without using VBA is a combination of data validation and formatting. Use Data Validation to allow only whole numbers in the cells. Set the minimum to the smallest grant number and the maximum to 99999999 if grant numbers can have up to eight digits. Then format the cells with custom format
For this approach you don't need to save your workbook as a macro-enabled spreadsheet and it works on all devices, i.e. web, mobile and desktop Excel.
If you do want to use code, here is a simpler version that also works on multiple cells at a time, if data is copied and pasted, for example. It looks for the numbers entered into column A, so you need to change that to the column you need.
To apply the code, copy it, then right-click the sheet tab, select View Code and paste the code into the big white code window.
Private Sub Worksheet_Change(ByVal Target As Range) Dim cel As Range Dim padding As String If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False For Each cel In Target padding = "00000000" padding = Left(padding, 8 - Len(cel.Value)) cel = "G" & padding & cel.Value Next cel Application.EnableEvents = True End If End Sub
09-24-2019 07:11 PM - edited 09-24-2019 07:13 PM
You should place that code on Sheet Module and to do that refer to the following image to know the steps required.
1) Right click on Sheet Tab.
2) Choose View Code and it will open a code window.
3) Paste the code into the opened code window.
09-25-2019 04:21 PM
@amandaramos The file you attached does not use any data validation.
What part of my approach does not work?
Data validation works only when data is entered into a cell. If you already have text in a cell, applying data validation for whole numbers will not change the text in the cell. Also, custom number formats won't work in the cells.
I see you have applied the number format. Now you need to turn the data into numbers. For most of the data you can use this approach:
- select column L
- use Find and Replace
- Find "G?" (that is a capital G and a space)
- Replace with -- leave that empty
- Click Replace All
There are a few cells where the space is a special non-breaking character, not the regular space character. Therefore, use the wildcard character ? in the find box.
Now the cells have been converted to numbers and show the custom format.
As a next step, apply the data validation to the whole column, so any new grant number entered can only use whole numbers.
by Brianjafthabj6 on August 11, 2020
by Guy Hunkin on August 05, 2020
by Sonia Atchison on July 24, 2020
Posted in Microsoft Ignite The Tour 2019 on February 14, 2020