[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Convert "P12" to "P0012"

Posted on 2014-04-01
Medium Priority
141 Views
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
Question by:Patrick O'Dea

LVL 13

Expert Comment

ID: 39970250
Hi,

[
``````=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)))
``````
0

LVL 24

Accepted Solution

Ejgil Hedegaard earned 2000 total points
ID: 39970381
Or use

="P"&TEXT(RIGHT(A1,LEN(A1)-1),"0000")
0

Author Closing Comment

ID: 39970412
Perfect
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month19 days, 16 hours left to enroll