no need space

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
1,010
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1233.83244 AB 233.83244 AB
2539.0 p36.5 p50.7 AB p539.0 p 36.5 p 50.7pAB Need p or r in center but No space between letters
35116.9 p14.0 p22 b p5116.9 p 14.0 p 22 p b Rest is fine
4843.43.66.7 p843.43.66.7 p
5821.4 AC r15.3 r821.4rAC15.3 r
62044.9 B r34.2 p50 B p2044.9rB 34.2 p 50pB
Sheet1
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
Assuming 1) it was mistake
2) no,
3a) and 3b) no, try in F1 (in Microsoft 365):
Excel Formula:
=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
 
Upvote 0
Assuming 1) it was mistake
2) no,
3a) and 3b) no, try in F1 (in Microsoft 365):
Excel Formula:
=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
 
Upvote 0
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:

Excel Formula:
=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:

Excel Formula:
=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))))


1727331381112.png
 
Upvote 1
Solution
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:

Excel Formula:
=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:

Excel Formula:
=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 :)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top