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
Patrick O'DeaAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
Or use

="P"&TEXT(RIGHT(A1,LEN(A1)-1),"0000")
0
 
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
 
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.

All Courses

From novice to tech pro — start learning today.