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

x
?
Solved

Convert "P12" to "P0012"

Posted on 2014-04-01
3
Medium Priority
?
141 Views
Last Modified: 2014-04-01
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
Comment
Question by:Patrick O'Dea
3 Comments
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39970250
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
 
LVL 24

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 39970381
Or use

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

Author Closing Comment

by:Patrick O'Dea
ID: 39970412
Perfect
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question