formula to count number of characters after a specified character

[buddigars]

COLUMN A	COUMN B
TX-ABC-12345	TX-ABC-123-45
TX-AB-135690	TX-AB-135-690
PP-BC-1234	PP-BC-123-4
AB-DEF-01	AB-DEF-01
AB-DEF	AB-DE-F

<tbody>
</tbody>

hi forum members,

I have a data as above in column A. I want the target data as column B.
My requirement is to count the number of characters after last hyphen. If the characters are >=4 then add a hyphen after 3 characters and display the total string.

please help me with a formula.

 

[Pup Denab]

HI Buddigars
Welcome to the forum

=IF(RIGHT(IFERROR(IF(LEN(A2)-FIND("-",A2,4)>=4,LEFT(A2,FIND("-",A2,4))&MID(A2,FIND("-",A2,4)+1,3),A2)&"-"&MID(A2,LEN(LEFT(A2,FIND("-",A2,4))&MID(A2,FIND("-",A2,4)+1,3))+1,99),A2),1)="-",IFERROR(IF(LEN(A2)-FIND("-",A2,4)>=4,LEFT(A2,FIND("-",A2,4))&MID(A2,FIND("-",A2,4)+1,3),A2)&MID(A2,LEN(LEFT(A2,FIND("-",A2,4))&MID(A2,FIND("-",A2,4)+1,3))+1,99),A2),IFERROR(IF(LEN(A2)-FIND("-",A2,4)>=4,LEFT(A2,FIND("-",A2,4))&MID(A2,FIND("-",A2,4)+1,3),A2)&"-"&MID(A2,LEN(LEFT(A2,FIND("-",A2,4))&MID(A2,FIND("-",A2,4)+1,3))+1,99),A2))

This formula in B2 will do as you ask, however your AB-DEF to AB-DE-F is wrong by you criteria, I have corrected as you requested it should be AB-DEF AB-DEF

 

[Domenic]

As Pup has already pointed out, it looks like the example for your last text string is not correct. If so, here's another way...

=IF(LEN(MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,LEN(A1)))>=4,REPLACE(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+4,0,"-"),A1)

Hope this helps!

 

[Scott Huish]

Here's another way:

=IF(LEN(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255)))>3,REPLACE(A1,LEN(A1)-2,0,"-"),A1)

 

[Rick Rothstein]

Here's another way:

=IF(LEN(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255)))>3,REPLACE(A1,LEN(A1)-2,0,"-"),A1)

I don't think that works correctly Scott (check what your formula returns for the first and third ones compared to what the OP said was wanted).

 

[Rick Rothstein]

Here is another formula that appears to work correctly...

=LEFT(A1,FIND("-",A1&"-",FIND("-",A1)+1)+3)&IF(LEN(A1)>FIND("-",A1&"-",FIND("-",A1)+1)+3,"-"&MID(A1,FIND("-",A1&"-",FIND("-",A1)+1)+4,9),"")

 

[ttdk1]

here's another way

=IFERROR(MID(A1,1,FIND("-",A1,5)+3)&IFERROR("-"&RIGHT(A1,LEN(A1)-FIND("-",A1,5)-3),""),A1)

 

[barry houdini]

Cross-posted here

 

[Rick Rothstein]

here's another way

=IFERROR(MID(A1,1,FIND("-",A1,5)+3)&IFERROR("-"&RIGHT(A1,LEN(A1)-FIND("-",A1,5)-3),""),A1)

Here is where more information about what the OP's data looks like would have been helpful... your formula will fail if the character count before the second dash is 4-3, 3-4 or larger.

EDIT NOTE: My concern turns out to be the case if you check out the cross-posted link Barry posted. As a matter of fact, the dash count is not even consistent!

 

[Rick Rothstein]

Cross-posted here

@Barry,

Sigh! Why do people keep doing this to us.


@buddigars,

Thank you so much for wasting our time here by, first, not telling us you cross-posted your question (so we could not check out other submissions before developing our own) and, second, for not using the same example data (you posted examples with more dashes and longer text sections than you did here).