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

 

[dreid1011]

Perhaps this:

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

Cell Formulas
Range	Formula
F2:F18	F2	=A2
G2:G18	G2	=TEXT(B2,"0.0")&" "&D2
K2:K18	K2	=TRIM(LEFT(B2,FIND(" ",B2&" "))&" "&D2&" "&IF(ISNUMBER(FIND(" ",B2)),RIGHT(B2,LEN(B2)-FIND(" ",B2)),""))

 

[Phuoc]

Try this:

Book1
	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
5	51.9	17.7				51.9	17.7		17.7
6	61	22.5		p		61	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 p AC		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 p A		10.6 p A
14	88.3	5.4 C				88.3	5.4 C		5.4 C
15	87	2.9				87	2.9		2.9
16	87	6.7 AC		p		87	6.7 p AC		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	=TRIM(SUBSTITUTE(TEXT(B2,"0.0 ")," "," "&D2&" ",1))

 

[kvsrinivasamurthy]

In K2 for 365 version

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

Adjust the range as required

 

[sksanjeev786]

Perhaps this:

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

Cell Formulas
Range	Formula
F2:F18	F2	=A2
G2:G18	G2	=TEXT(B2,"0.0")&" "&D2
K2:K18	K2	=TRIM(LEFT(B2,FIND(" ",B2&" "))&" "&D2&" "&IF(ISNUMBER(FIND(" ",B2)),RIGHT(B2,LEN(B2)-FIND(" ",B2)),""))

Thank you so much for your help with this...

 

[sksanjeev786]

In K2 for 365 version

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

Adjust the range as required

Thank you so much for your help with this...

 

[dreid1011]

Thank you so much for your help with this...

You're welcome, and happy to help. The other solutions are much cleaner than mine though.

 

[sksanjeev786]

Try this:

Book1
	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
5	51.9	17.7				51.9	17.7		17.7
6	61	22.5		p		61	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 p AC		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 p A		10.6 p A
14	88.3	5.4 C				88.3	5.4 C		5.4 C
15	87	2.9				87	2.9		2.9
16	87	6.7 AC		p		87	6.7 p AC		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	=TRIM(SUBSTITUTE(TEXT(B2,"0.0 ")," "," "&D2&" ",1))

Hi

I need few changes on above data all good with the data just I need "34.6 p AC " as 34.6pAC (without space if I have any letter after p

 

[Sanjeev1976]

You can use the below formula :

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

 

[sksanjeev786]

You can use the below formula :

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

Hi Sir,

Thank you so much for checking on this

but I need to remove space only on if we have data in this format "34.6 p AC " to 34.6pAC.

And for this type of data will remain the same "5.4 A or 54. p with 1 space...

Only when "p" is coming in center along with Data and letter then only I need to remove Space (34.6pAC) and rest will be same with 1 space