no need space

[sksanjeev786]

Hi Team

I have a data in A to D and my output is F to I

i need p or q in center with out space refer example in F to I column

book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	2	33.8	32	44 AB		2	33.8	32	44 AB
2	5	39.0 p	36.5 p	50.7 AB p		5	39.0 p	36.5 p	50.7pAB			Need p or r in center but No space between letters
3	51	16.9 p	14.0 p	22 b p		51	16.9 p	14.0 p	22 p b			Rest is fine
4	84	3.4	3.6	6.7 p		84	3.4	3.6	6.7 p
5	82	1.4 AC r	1	5.3 r		82	1.4rAC	1	5.3 r
6	20	44.9 B r	34.2 p	50 B p		20	44.9rB	34.2 p	50pB
Sheet1

 

[Kaper]

What is so special about D3 value that in I3 in sample we can see result 22 p b not 22pb ?

Are there also values like
16 AB B
(3 space_separated segments with other letter than p or r at the end)
or for instance 4 space separated segments like
16 AB B p
or
16 AB p A

 

[Kaper]

Assuming 1) it was mistake
2) no,
3a) and 3b) no, try in F1 (in Microsoft 365):

=LET(a,TEXTSPLIT(A1," "),b,COLUMNS(a),IF(b=3,CONCAT(INDEX(a,1,1),INDEX(a,1,3),INDEX(a,1,2)),A1))

and copy down/right

 

[sksanjeev786]

Assuming 1) it was mistake
2) no,
3a) and 3b) no, try in F1 (in Microsoft 365):

=LET(a,TEXTSPLIT(A1," "),b,COLUMNS(a),IF(b=3,CONCAT(INDEX(a,1,1),INDEX(a,1,3),INDEX(a,1,2)),A1))

and copy down/right

Hey Kaper,

All good in the formula for but I need a Space in 44 AB

only if the cell contains p,q, and r along with other letters (Wingding 3 arrow) then only I don't need any space.
Otherwise 1 space is required after data.. (if no p,q r) available on data

i.e
44 AB
44pAB
44 p
44 q
44 r
44rDEF

 

[Kaper]

Have you checked the formula? It will not remove space from 44 AB

 

[sksanjeev786]

Have you checked the formula? It will not remove space from 44 AB

Hi,

I need a space if cell doesn’t have p q or r so 44 AB i need space

 

[Kaper]

OK, I can see now what you ment. Your "44 AB" has a space at the end so in a cell you have "44 AB "
Formula changed to take care of such situations by trimming unneeded spaces from front/back would be:

=LET(a,TEXTSPLIT(TRIM(A1)," "),b,COLUMNS(a),IF(b=3,CONCAT(INDEX(a,1,1),INDEX(a,1,3),INDEX(a,1,2)),A1))

There is still difference between result in D3:

22 b p

Shall it become (as described in text)

22pb

?

or as you've shown in your example

22 p b

(and if so why here was no moving of p between other elements and spaces removal)?

As you might notice so far, in formula there was no test for third part being p or r (or as you mentioned in post #6: q)
That's because in your sample there was no such case like 1 A B, etc. (see my screenshot cell A7).
But anticipating this could happen, there is even longer formula for you to try:

=LET(a,TEXTSPLIT(TRIM(A1)," "),b,COLUMNS(a),IF(OR(b<>3,ISERROR(SEARCH(INDEX(a,1,3),"pqr"))),A1,CONCAT(INDEX(a,1,1),INDEX(a,1,3),INDEX(a,1,2))))

 

[sksanjeev786]

OK, I can see now what you ment. Your "44 AB" has a space at the end so in a cell you have "44 AB "
Formula changed to take care of such situations by trimming unneeded spaces from front/back would be:

=LET(a,TEXTSPLIT(TRIM(A1)," "),b,COLUMNS(a),IF(b=3,CONCAT(INDEX(a,1,1),INDEX(a,1,3),INDEX(a,1,2)),A1))

There is still difference between result in D3:

22 b p

Shall it become (as described in text)

22pb

?

or as you've shown in your example

22 p b

(and if so why here was no moving of p between other elements and spaces removal)?

As you might notice so far, in formula there was no test for third part being p or r (or as you mentioned in post #6: q)
That's because in your sample there was no such case like 1 A B, etc. (see my screenshot cell A7).
But anticipating this could happen, there is even longer formula for you to try:

=LET(a,TEXTSPLIT(TRIM(A1)," "),b,COLUMNS(a),IF(OR(b<>3,ISERROR(SEARCH(INDEX(a,1,3),"pqr"))),A1,CONCAT(INDEX(a,1,1),INDEX(a,1,3),INDEX(a,1,2))))

View attachment 117329

Perfect! its working... thank you so much

Appreciate your time and hard work

 

[Kaper]

Glad we did it