MS Excel formula to replace space

I am looking for an excel formula for following (Excel 2016 - If that ever matters)
    Prefix "Doc" to  cell A2 value
     Replace space in column A value by _   ( But if there are two or more space have only one _ )
     Write the value to cell B2

E.x  My Single space  should become My_Single_Space
       My  DoubleSpace    turns to  My_DoubleSpace       ( Even though there are two spaces, only one _)
       My   TripleSpace singlespace      turns to My_TripleSpace_SingleSpace
Sam OZAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
=SUBSTITUTE(TRIM(A1)," ","_")

Change A1 to wherever your data is
0
 
NorieConnect With a Mentor VBA ExpertCommented:
Perhaps.

="Doc"&SUBSTITUTE(TRIM(A2)," ","_")
0
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Maybe this...
Assuming your string is in A2, then try this...
="Doc_"&SUBSTITUTE(SUBSTITUTE(TRIM(A2),CHAR(160),"")," ","_")

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Sam OZAuthor Commented:
The real challenge with me is that whether it is one or two or three space, I should get only one _   . I don't think any of the solution is doin it
0
 
Saqib Husain, SyedEngineerCommented:
All of them do it. Send an example file showing your problem.
1
 
NorieVBA ExpertCommented:
The TRIM function, that all the suggested formulas use, will change multiple spaces within a string to single spaces.

If that's not happening something else must be going on, perhaps the 'spaces' aren't some other non-printing character.
0
 
Sam OZAuthor Commented:
My aplogies! that is right
0
All Courses

From novice to tech pro — start learning today.