need p letter in between number and letter

[sksanjeev786]

Hi Team

I have data in Column A and B and output coming in Column G but I need data as per column I like I10, I13 and I16 cells like where ever I have data wiht numbers and letter I need p and r should come in the center please ref yellow highlighted

book2
	A	B	C	D	E	F	G	H	I
1									But I need as
2	9.1	27.6		p		9.1	27.6 p		27.6 p
3	14.3	33.8		p		14.3	33.8 p		33.8 p
4	93.5	4				93.5	4.0		4.0
5	51.9	17.7				51.9	17.7		17.7
6	61	22.5		p		61.0	22.5 p		22.5 p
7	90.9	2.8				90.9	2.8		2.8
8	89.6	2.8				89.6	2.8		2.8
9	68.8	10.9				68.8	10.9		10.9
10	32.5	34.6 AC		p		32.5	34.6 AC p		34.6 p AC
11	76.6	1.9				76.6	1.9		1.9
12	70.1	10.9				70.1	10.9		10.9
13	80.5	10.6 A		p		80.5	10.6 A p		10.6 p A
14	88.3	5.4 C				88.3	5.4 C		5.4 C
15	87	2.9				87.0	2.9		2.9
16	87	6.7 AC		p		87.0	6.7 AC p		6.7 p AB
17	88.3	5.4 A				88.3	5.4 A		5.4 A
18	89.6	6.6				89.6	6.6		6.6
Sheet1

Cell Formulas
Range	Formula
F2:F18	F2	=A2
G2:G18	G2	=TEXT(B2,"0.0")&" "&D2

 

[Sanjeev1976]

You can try the below :

=MAP(B2:B25,D2:D25,LAMBDA(x,y,LET(a,TEXTSPLIT(x&" ",," "),IF(INDEX(a,2)="",INDEX(a,1)&" ",INDEX(a,1)&"")&y&""&INDEX(a,2))))

 

[sksanjeev786]

You can try the below :

=MAP(B2:B25,D2:D25,LAMBDA(x,y,LET(a,TEXTSPLIT(x&" ",," "),IF(INDEX(a,2)="",INDEX(a,1)&" ",INDEX(a,1)&"")&y&""&INDEX(a,2))))

thank you so much for you help and support but I can see no space between 5.4C or 5.4A I need space for those things except where ever p is coming in center

5.4C

2.9

6.7pAC

5.4A

6.6

 

[Sanjeev1976]

Please ignore the above and use the below formula :

=MAP(B2:B25,D2:D25,LAMBDA(x,y,LET(a,TEXTSPLIT(x&" ",," "),IF(INDEX(a,2)="",INDEX(a,1)&" "&y,INDEX(a,1)&""&IF(y=""," "&INDEX(a,2),y&""&INDEX(a,2))))))

 

[sksanjeev786]

Please ignore the above and use the below formula :

=MAP(B2:B25,D2:D25,LAMBDA(x,y,LET(a,TEXTSPLIT(x&" ",," "),IF(INDEX(a,2)="",INDEX(a,1)&" "&y,INDEX(a,1)&""&IF(y=""," "&INDEX(a,2),y&""&INDEX(a,2))))))

Thanks you so much Sir for your help with this