PRI to show when entering text in a cell

[Shazz]

Hi All,

I want to type say 042/18 into any cell in column F and need to to automatically show as PRI042/18, can anyone advise how I go about this please.

I have only basic knowledge of Formulas and Codes.

Thanks

Shazz x

 

[MrIfOnly]

If you're open to using VBA here is a solution:

Paste the following into the applicable worksheet module (right-click on worksheet tab, select "View Code"):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing And Left(Target.Value, 3) <> "PRI" Then
    Target.Value = "PRI" & Target.Value
End If
End Sub

Regards,

CJ

 

[My Aswer Is This]

1.Selects the cells you want:

2.Right click and choose Format Cells
3. Choose Custom
4. In the dialog box where you see Type below there in the line enter:

PRI​

 

[Shazz]

1.Selects the cells you want:

2.Right click and choose Format Cells
3. Choose Custom
4. In the dialog box where you see Type below there in the line enter:

PRI​

I tried this and when I typed 118/18 it just changed the cell to PRI, I need it to read PRI118/18. I dont want to have to save as a Macro enabled version so VBA is not an option.

Any suggestions?

Shazz

 

[My Aswer Is This]

Well if your entering a number like 123456 and you want it to look like PRI123456

You would use custom formatting and enter PRI0

But what your entering like

118/18​

Is not seen as a number so my solution will not work.
Sorry about that.
And I have no solution with out using Vba

Or Maybe someone else on the form here will be able to teach us both how to do this.

 

[vcoolio]

Hence, MrIfOnly's code in post #2 .

Cheerio,
vcoolio.

 

[My Aswer Is This]

User said:

I dont want to have to save as a Macro enabled version so VBA is not an option.​

Hopefully someone may have a solution without using vba.

 

[vcoolio]

However, in her opening post, Shazz says:-

I have only basic knowledge of Formulas and Codes.

which would infer that she is open to a VBA solution.

It could be done with a formula along the lines of:-

=IF(ISTEXT(A1),"PRI"&A1)

but the value (042/18) would need to be in one cell (A1 in the example) and the formula in the adjacent cell (B1).

 

[Fluff]

If you enter the value as4218 rather than 042/18 you can use custom format
"PRI"000"/"##

 

[Shazz]

If you enter the value as4218 rather than 042/18 you can use custom format
"PRI"000"/"##

Thankyou that is exactly what I was after, your a star.

Shazz
xx