Help with excel

[amandaramos]

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 

[#2]

@amandaramos 

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.

[#3]

Hi @Subodh_Tiwari_sktneer  where do I place this code? 

Thanks, 

Amanda

[#4]

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

[#5]

@amandaramos 

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.

[#6]

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. 

[#7]

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