Adding in Characters Either Once or Twice

[rebel123]

Thanks for your help...

Adding in Characters Either Once or Twice.
The correct answers are highlighted in green.
As you can see, there are either one or two underlines added.
I have the correct answer, but I need a drag-down formula please.

You can detect when to add them because basically you are substituting the space with an underline.

Thanks!

 

[Rick Rothstein]

Try this...

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

 

[rebel123]

Thanks. It works, but the underline in the 2nd part is too long...

 

[rebel123]

As you can see, your formula is in yellow...

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	D	E
1
2	014 200000000 0000608	014_200000000_0000608
3	051 020000000 0000300	051_020000000_0000300
4	062E090320001 0003500	062E090320001_0003500
5	084E20017000M 0000200	084E20017000M_0000200
Sheet872

Cell Formulas
Range	Formula
E2:E5	E2	=SUBSTITUTE(TRIM(D2)," ","_")

 

[rebel123]

I tried the last one and same issue... there are 2 underlines... I have no idea why!

 

[rebel123]

maybe do the sequence and delete the 2nd underline and substitute it with one... or u can do a 3 column answer and then the third answer is combined...

 

[jtakw]

I Edited a minute after posting, try again.

 

[rebel123]

Although visually it looks like the 2nd sequence has 1 space... it has 2 when i hover in the formula...

 

[rebel123]

Thanks Rick and jtakw.
You are both technically correct, since you had no way of knowing about the extra space...

So I just liked both of your posts and followed you both.
Many thanks!