Substitute a X character more than 1 instance and from different directions

Try this one

=SUBSTITUTE(LEFT(A1,FIND(" ",A1))&"|"," ","")&TRIM(MID(A1,FIND(" ",A1)+1,FIND("/",A1)-3-FIND(" ",A1)-5))&"|"&SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*4))," ","|",2)

	A	B
1	1 Kick-off Meeting Tue 04/01/14 Tue 04/01/14	1|Kick-off Meeting|Tue 04/01/14|Tue 04/01/14
2	2 Site survey and assessment Thu 04/06/14 Fri 04/07/14	2|Site survey and assessment|Thu 04/06/14|Fri 04/07/14
3	16 Drawing Analysis Mon 04/21/14 Fri 04/25/14	16|Drawing Analysis|Mon 04/21/14|Fri 04/25/14

Sorry, I did adjust formula on the original post. It should work fine now.

Hi

To make things easier, i suggest a Helper column (see column D in the below example)

	A	B	C	D
1	Text	New Text		Spaces
2	1 Kick-off Meeting Tue 04/01/14 Tue 04/01/14	1|Kick-off Meeting|Tue 04/01/14|Tue 04/01/14		6
3	2 Site survey and assessment Thu 04/06/14 Fri 04/07/14	2|Site survey and assessment|Thu 04/06/14|Fri 04/07/14		8
4	16 Drawing Analysis Mon 04/21/14 Fri 04/25/14	16|Drawing Analysis|Mon 04/21/14|Fri 04/25/14		6
5	10 Define specs Mon 16/06/14 Mon 16/06/14	10|Define specs|Mon 16/06/14|Mon 16/06/14		6
6	20 Set parameters Mon 16/06/14 Mon 16/06/14	20|Set parameters|Mon 16/06/14|Mon 16/06/14		6
7	30 Place PO Mon 16/06/14 Mon 16/06/14	30|Place PO|Mon 16/06/14|Mon 16/06/14		6
8	40 Hire Consultant and Providers Mon 16/06/14 Mon 16/06/14	40|Hire Consultant and Providers|Mon 16/06/14|Mon 16/06/14		8

Formula in D2 copied down
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

Formula in B2 copied down
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ","|",1)," ","|",D2-2)," ","|",D2-4)

Hope this helps
(Espero que ajude )

M.

Thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).