Trim function

Mr. Guy · May 7th, 2002, 02:20 PM

I have some cells that look like this after using the concatenate function:
101-P T -200
102-L T -201
103-MOV-202

What I'm trying to do is get all the cells to display the tags with no spaces in them (like the MOV tag). I tried using the TRIM function but it leaves one space between the letters. Is there something similar to TRIM that would take all of the spaces out?

Mark W. · May 7th, 2002, 02:24 PM

See the Help topic for the SUBSTITUTE worksheet function.

Hint: change " " to "".

Colo · May 7th, 2002, 10:33 PM

Hi Mr. Guy,
The answer is see columns "G" of following.

Microsoft Excel - Trim function.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
G4		=	=SUBSTITUTE(F4," ","")
*	A	B	C	D	E	F	G
1	*	*	*	*	*	*	Answer is
2	101	-	P T	-	200	=CONCATENATE(A2,B2,C2,D2,E2)	=SUBSTITUTE(F2," ","")
3	102	-	L T	-	201	=CONCATENATE(A3,B3,C3,D3,E3)	=SUBSTITUTE(F3," ","")
4	103	-	MOV	-	202	=CONCATENATE(A4,B4,C4,D4,E4)	=SUBSTITUTE(F4," ","")

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 7th, 2002, 02:20 PM	#1
Mr. Guy New Member Join Date: Mar 2002 Posts: 18	I have some cells that look like this after using the concatenate function: 101-P T -200 102-L T -201 103-MOV-202 What I'm trying to do is get all the cells to display the tags with no spaces in them (like the MOV tag). I tried using the TRIM function but it leaves one space between the letters. Is there something similar to TRIM that would take all of the spaces out?

May 7th, 2002, 02:24 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	See the Help topic for the SUBSTITUTE worksheet function. Hint: change " " to "".