Excel Formula Needed for Number and Letter Sequence

[rebel123]

Excel Formula Needed for Number and Letter Sequence.
My answers are in the blue area but I do not know a formula
that I can drag down. It might be a SUBSTITUTE formula.

Please help!

 

[kweaver]

Do you just want to change the period to an underscore?

=SUBSTITUTE(D2,".","_")

Filled down??

And the blanks to an underscore as well?

=SUBSTITUTE(SUBSTITUTE(D2,".","_")," ","_")

 

[rebel123]

The problem is that the first and third one

1-353N-07-017.01
and
1-353N-07-021.13

Have the letter after 353.
So both of those are 353N.

Those sequences only need the endings
which are _01 and _13

BUT

The problem is that the other ones
need the extra spacings after the 6th character and the
3rd to last character

1-434(UNDERSCORE)-18-016(UNDERSCORE)00
1-624(UNDERSCORE)-13-042(UNDERSCORE)02


So, you would need some sort of crazy formula that would
detect the 6th character....The 6th Character is "N" so if
the 6th character is any letter, then it only needs 1 underscore
at the end...

However, if the 6th character is NOT a letter then it would need
2 underscores in the exact place within that sequence.

 

[rebel123]

The 6th character in the other 2 sequences are technically just blank spaces for both.

 

[kweaver]

Isn't that what I did?
If not, you should post a more detailed set of examples (please use XL2BB when posting them).

 

[rebel123]

It would need to be a drag and drop formula.... You technically have the right answers but i would need to separate the whole column and separate the letter sequences.... The letter sequences are the 353N one... So there probably is a formula that i could put in the blue area of the image and drag it down and it would work....??

 

[kweaver]

If you don't want Column E as shown, what do you want?

 

[rebel123]

Maybe im reading this wrong but u have 2 formulas.... I have no idea how VBAs or programs work so i would need one formula starting with an equals sign

 

[rebel123]

The formula I see is
=SUBSTITUTE(D2,".","_")

And


=SUBSTITUTE(SUBSTITUTE(D2,".","_")," ","_")

 

[kweaver]

This is not VBA, it's a formula for E2 and filled down

 =SUBSTITUTE(SUBSTITUTE(D2,".","_")," ","_")