Variable number formatting formula

[CaptainGravyBum]

Hello,
I have a formula in column A which is building a specific number format based on the characters provided from column B. This has been working fine when it was a fixed amount of numbers, but the spanner in the works is we now have text in addition to a the number. I've tried to adjust my formula to accommodate, but I can't get it right. Can anyone help?

Book1.xlsx
	A	B
1	+++85931489-02	85931489-2
2	+++85947129-01	85947129-1
3	GECV8595251101	GECV85952511-1
4	GECV8595251101	GECV85952511-1
Sheet1

Cell Formulas
Range	Formula
A1:A4	A1	=IF(ISNUMBER(SEARCH("GEC",B1)),TEXT(LEFT(B1,12),"+++####00000000-")&TEXT(RIGHT(B1,LEN(B1)-FIND("-",B1)),"00"),TEXT(LEFT(B1,8),"+++00000000-")&TEXT(RIGHT(B1,LEN(B1)-FIND("-",B1)),"00"))

 

[Joe4]

It looks like you have shown us what it is currently returning.
Can you show us what you WANT it to return?

Also, please sure to show us any other structures and desired results, for any other entries you may have that don't fall into one of the two format/structures you have shown in your example.

 

[Alex Blakenburg]

Would something like this work for you ?

=LET(bIsTxt,ISERROR(--LEFT(B1,1)),
     iStart,IF(bIsTxt,5,1),
     IF(bIsTxt,LEFT(B1,iStart-1),"") & TEXT(--MID(B1,iStart,8),"+++00000000-") & TEXT(TEXTAFTER(B1,"-"),"00"))

 

[CaptainGravyBum]

It looks like you have shown us what it is currently returning.
Can you show us what you WANT it to return?

Also, please sure to show us any other structures and desired results, for any other entries you may have that don't fall into one of the two format/structures you have shown in your example.

There will only be these two structures in the data.
The lines without text should be in this format
+++85947129-01
and the lines with text will need to be
+++GECV85952511-01

 

[CaptainGravyBum]

Would something like this work for you ?

=LET(bIsTxt,ISERROR(--LEFT(B1,1)),
     iStart,IF(bIsTxt,5,1),
     IF(bIsTxt,LEFT(B1,iStart-1),"") & TEXT(--MID(B1,iStart,8),"+++00000000-") & TEXT(TEXTAFTER(B1,"-"),"00"))

Hi Alex,
This is close, but the +++ needs to be at the beginning, I have added example results above.

 

[Joe4]

How about this:

="+++" & LEFT(B1,FIND("-",B1)) & TEXT(TEXTAFTER(B1,"-"),"00")

Results:

 

[CaptainGravyBum]

How about this:

="+++" & LEFT(B1,FIND("-",B1)) & TEXT(TEXTAFTER(B1,"-"),"00")

Results:
View attachment 114188

Hi @Joe4 ,
That formula is a lot simpler than the tat I came up with and works a treat! Thanks for your help.

 

[Joe4]

You are welcome.
Glad we were able to help!