SOLVED

How to join each word of a sentence and make one word?

%3CLINGO-SUB%20id%3D%22lingo-sub-2390783%22%20slang%3D%22en-US%22%3EHow%20to%20join%20each%20word%20of%20a%20sentence%20and%20make%20one%20word%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2390783%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings!%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20item%20code%2C%20having%20more%20than%20300%20items.%20I%20want%20each%20word%20of%20a%20cell%20would%20be%20a%20single%20word.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFOR%20EXAMPLE%2C%3C%2FSTRONG%3E%20A1%20Cell%20value%20is%20%3CSTRONG%3E(DC%20ALASKA%20PINK)%3C%2FSTRONG%3E.%20I%20WANT%20%3CSTRONG%3E(DCALASKAPINK)%3C%2FSTRONG%3E.%26nbsp%3B%20meaning%20joining%20each%20word.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20give%20me%20an%20idea%2C%20how%20to%20do%20this%3F%20I%20tried%20JOINT%2C%20CONCATENATE%20options..%20but%20failed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20Advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2390783%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2390866%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20join%20each%20word%20of%20a%20sentence%20and%20make%20one%20word%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2390866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064611%22%20target%3D%22_blank%22%3E%40najim_RAKCERAMICS%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUBSTITUTE(A1%2C%20%22%20%22%2C%20%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewill%20return%20the%20value%20of%20A1%20with%20all%20spaces%20removed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Greetings!

I have a list of item code, having more than 300 items. I want each word of a cell would be a single word. 

 

FOR EXAMPLE, A1 Cell value is (DC ALASKA PINK). I WANT (DCALASKAPINK).  meaning joining each word. 

 

Can anyone give me an idea, how to do this? I tried JOINT, CONCATENATE options.. but failed. 

 

Thanks in Advance.

3 Replies
best response confirmed by najim_uddin (Occasional Contributor)
Solution

@najim_uddin

The formula

 

=SUBSTITUTE(A1, " ", "")

 

will return the value of A1 with all spaces removed.

Thanks, @Hans Vogelaar, this formula works. can you explain how does it working?

@najim_uddin 

See SUBSTITUTE function 

SUBSTITUTE(A1, " ", "") means: take the value of cell A1 and replace all occurrences of " " (a space) with "" (an empty string)