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, SyedEngineerCommented:
=SUBSTITUTE(TRIM(A1)," ","_")

Change A1 to wherever your data is
0
 
NorieVBA ExpertCommented:
Perhaps.

="Doc"&SUBSTITUTE(TRIM(A2)," ","_")
0
 
Subodh Tiwari (Neeraj)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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.