Solved

Convert "P12" to "P0012"

Posted on 2014-04-01
3
115 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 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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