Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

Convert "P12" to "P0012"

Hi,

Simple enough string manipulation here.

I am looking for a neat way to convert text fields to the format P9999.  4 digit prefixed by "P".

See before and after examples below.

P12 to P0012
P1 to P0001
P123 to P0123
P1234 to P1234
0
Patrick O'Dea
Asked:
Patrick O'Dea
1 Solution
 
Santosh GuptaCommented:
Hi,

Please try below....

[
=IF(LEN(a1)=2,"P000" & RIGHT(a1,LEN(a1)-1),IF(LEN(a1)=3,"P00" & RIGHT(a1,LEN(a1)-1),IF(LEN(a1)=4,"P0" & RIGHT(a1,LEN(a1)-1),a1)))

Open in new window

0
 
Ejgil HedegaardCommented:
Or use

="P"&TEXT(RIGHT(A1,LEN(A1)-1),"0000")
0
 
Patrick O'DeaAuthor Commented:
Perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now