Sep 23 2019 01:55 PM
Hello,
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.
Thank you,
Amanda
Sep 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.
Sep 24 2019 12:35 PM
Sep 24 2019 01:48 PM - edited Sep 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
\G00000000
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
Sep 24 2019 07:11 PM - edited Sep 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.
Sep 25 2019 08:30 AM
Hello, @Ingeborg Hawighorst
I like the fact that it doesn't need macros...since this file lives in SharePoint and it doesn't support macros. I followed your approach with data validation but it didn't work.
see my file attached.
Sep 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.