Advanced LEFT/RIGHT/REPLACE function for formatting

[sheller]

Hi everyone,
I am trying to standardize items listed in different formats (people didn't enter consistently) and can't figure out an easy way to use a function to obtain the correct formatting. See screen shot below with entry examples (column A) and needed output (column B). Is there any way to enter a function(s) to achieve outcome listed in column B (I manually entered for sake of this question)? Thanks much in advance!

COL A COL B
Iterations Needed Format: P-XXXX-A (unless different letter listed in coulmn A)
1234 P-1234-A
P1235 P-1235-A
1236-A P-1236-A
1236-B P-1236-B
1237 P-1237-A
P1237C P-1237-C

 

[Eric W]

Maybe:

	A	B
1	1234	P-1234-A
2	P1235	P-1235-A
3	1236-A	P-1236-A
4	1236-B	P-1236-B
5	1237	P-1237-A
6	P1237C	P-1237-C
7	555	P-555-A
8	788G	P-788-G
9	P-66666-I	P-66666-I
10	Q33asdf-X	P-33-X
11		P-0-

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula B1 {="P-"&MAX(IFERROR(MID(A1,{1,2,3},{1;2;3;4;5;6;7;8;9})+0,0))&"-"&IF(ISERROR(RIGHT(A1)+0),RIGHT(A1),"A")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This assumes the number will start no later than the 3rd position, and isn't longer than 9 digits.

 

[sheller]

It worked! Thank you very much, Eric W!! What a huge help.

Maybe:

	A	B
1	1234	P-1234-A
2	P1235	P-1235-A
3	1236-A	P-1236-A
4	1236-B	P-1236-B
5	1237	P-1237-A
6	P1237C	P-1237-C
7	555	P-555-A
8	788G	P-788-G
9	P-66666-I	P-66666-I
10	Q33asdf-X	P-33-X
11		P-0-

<tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula B1 {="P-"&MAX(IFERROR(MID(A1,{1,2,3},{1;2;3;4;5;6;7;8;9})+0,0))&"-"&IF(ISERROR(RIGHT(A1)+0),RIGHT(A1),"A")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This assumes the number will start no later than the 3rd position, and isn't longer than 9 digits.

 

[Rick Rothstein]

Maybe:

	A	B
1	1234	P-1234-A
2	P1235	P-1235-A
3	1236-A	P-1236-A
4	1236-B	P-1236-B
5	1237	P-1237-A
6	P1237C	P-1237-C
7	555	P-555-A
8	788G	P-788-G
9	P-66666-I	P-66666-I
10	Q33asdf-X	P-33-X
11		P-0-

<tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula B1 {="P-"&MAX(IFERROR(MID(A1,{1,2,3},{1;2;3;4;5;6;7;8;9})+0,0))&"-"&IF(ISERROR(RIGHT(A1)+0),RIGHT(A1),"A")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If we assume the example value you put in cell A10 is not one the OP has to handle (because his posted examples did not even hint at that shaped value), then this normally-entered formula will also work...

=IFERROR("P-"&MID(IF(ISNUMBER(-RIGHT(A1)),A1&"-A",IF(MID(A1,LEN(A1)-1,1)="-",A1,REPLACE(A1,LEN(A1),0,"-"))),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),"")

 

[sheller]

If we assume the example value you put in cell A10 is not one the OP has to handle (because his posted examples did not even hint at that shaped value), then this normally-entered formula will also work...

=IFERROR("P-"&MID(IF(ISNUMBER(-RIGHT(A1)),A1&"-A",IF(MID(A1,LEN(A1)-1,1)="-",A1,REPLACE(A1,LEN(A1),0,"-"))),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99),"")

Hi RIck,
Thanks very much! I love being able to use a 'normally-entered' formula such as yours above.