Custom Cell format

[iLuvbase]

I need to create a custom cell format that is immune to how someone enters types their data.

Basically we generate quotes on a Q# basis, but we may have multiple configurations for a single quote number.

e.q - we may have Q4457, Q4457A, Q4457B, C - etc.

However - coworkers enter data in different manners.

For example - if I enter 4457, I want the cell to display Q4457.

If I enter Q4457 I want it to read Q4457.

If i enter 4457A, I need it to read Q4457A.

If I enter Q4457A, I need it to read Q4457A.

Things I've tried -

Q# - Displays properly for 4457 or Q4457, and Q4457A - does not work for 4457A (Shows only 4457A, no leading Q)

Q@ - Displays properly for 4457 and 4457A - Does not work for Q4457 or Q4457A (displays 2 Qs at beginning)

 

[Ralajer]

Welcome to the board iLuvBase;

Try "Q"#;-#;0;"Q"@

Custom formats can have four fields Positive Numbers;Negative Numbers;Zero;Strings. The semicolon separates the format of each type

 

[iLuvbase]

Thank you for your attemp, but again same issues as above. Using your formula these are the inputs and ouputs I get

4457. = Q4457
Q4457=QQ4457
4457A=Q4457A
Q4457A=QQ4457A

 

[mikerickson]

You need a Change event, custom formatting won't do.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Cells.Count = 1 And Not (Application.Intersect(.Cells, Columns(1)) Is Nothing) Then
            If Not UCase(CStr(.Value)) Like "Q*" Then
                .Value = "Q" & CStr(.Value)
            Else
                .Value = "Q" & Mid(CStr(.Value), 2)
            End If
        End If
    End With
End Sub

But, it would be easier (and more robust) to use one of the custom formats and train your users to not input the intial Q.

 

[Ralajer]

Sorry, I read your post but it didn't register with me that Q may be inputted manual. That makes it so the formatting can't be done with Cell Formatting alone, as I understand it; however, it can be done with Cell Formatting and Conditional Formatting.

Select cell or range to apply format

Set the Cell format to: "Q"#;-#;0;"Q"@

Then in Conditional Formatting, New Rule

• Select Use Formulas to determine what cells to format
• In the Format values where this value is true box enter

=UPPER(LEFT(D2,1))="Q"
Where D2 corresponds to the active cell in your selected range​

• Press the format button and change the number format to

"Q"#;-#;0;@​

You can use the format painter to apply this elsewhere once you have it set up.

 

[pgc01]

Hi

Similar to Rob's.

Select A1, leave the format as General

In the conditional formatting, set the rule with the formula:

=ISNUMBER(-LEFT(A1,1))

and the custom format:

"Q"#;;;"Q"@

 

[AlexGo]

try also
in formatting rules under conditional format
=UPPER(LEFT(D1,1))<>"Q"
then
in custom format
"Q"#;;"Q"@

 

[iLuvbase]

Sorry, I read your post but it didn't register with me that Q may be inputted manual. That makes it so the formatting can't be done with Cell Formatting alone, as I understand it; however, it can be done with Cell Formatting and Conditional Formatting.

Select cell or range to apply format

Set the Cell format to: "Q"#;-#;0;"Q"@

Then in Conditional Formatting, New Rule

• Select Use Formulas to determine what cells to format
• In the Format values where this value is true box enter

=UPPER(LEFT(D2,1))="Q"
Where D2 corresponds to the active cell in your selected range​

• Press the format button and change the number format to

"Q"#;-#;0;@​

You can use the format painter to apply this elsewhere once you have it set up.

This worked great, thanks for the help. Pain keeping the older guys who are set in their ways happy.

 

[iLuvbase]

Well - I said it worked, and it did - just fine on my personal computer.

Neglected to mention that we are currently in the stone age with office software on company systems - and we are still on the ancient Excel 97 software. It does not have custom formatting available for conditional formatting.

Further suggestions? I tried the VBA code - but it didn't do anything. Am I missing something where I actually specify the cells that I want to enforce that change.

 

[Rick Rothstein]

Where did you try to put the VB code at? That code is "event" code, so it cannot be placed in a standard module (where macro go); rather, it must be placed in the worksheet's module. The easiest way to call up the worksheet module's code window is to go to that worksheet, right click the name tab at the bottom of the worksheet and click on "View Code" from the popup menu that appears... the code window that opens up when you do this is the one to copy/paste mikerickson's VB code into.