Extract Numbers and Text

[ChetanPuri]

Hi Excel Gurus

Is there a way where we can extract numbers from text but if there are no numbers we extract only text from the cell.

Many thanks
Regards
Chetan

 

[Rick Rothstein]

If you could show us some sample data and the results you want from then (so we don't have to guess), that would be helpful.

 

[ChetanPuri]

If you could show us some sample data and the results you want from then (so we don't have to guess), that would be helpful.

Thank you Rick, in column K, I want to extract Specific text such as L5.3 Column J (Row 8), if there's no number such as "Salary", I want it to return salary

Triennium Ops ^L0 Salaries Budget 24-26 V3 - 12 Sep 2023.xlsx
	J	K
1	Pay Rate Level
2	SALARY
3	SALARY
4	SALARY
5	SALARY
6	SALARY
7	SALARY
8	SCHADS L5.3
9	SCHADS L6.3
11	SALARY
12	SALARY
13	SCHADS L6.3
14	SCHADS L5.2
15	SCHADS L6.2
Salary Forecast-Updated Aug23

 

[Peter_SSs]

Maybe your sample data is not representative enough but for that data you could try this.

24 03 07.xlsm
	J	K
1	Pay Rate Level
2	SALARY	SALARY
3	SALARY	SALARY
4	SALARY	SALARY
5	SALARY	SALARY
6	SALARY	SALARY
7	SALARY	SALARY
8	SCHADS L5.3	L5.3
9	SCHADS L6.3	L6.3
10
11	SALARY	SALARY
12	SALARY	SALARY
13	SCHADS L6.3	L6.3
14	SCHADS L5.2	L5.2
15	SCHADS L6.2	L6.2
ChetanPuri

Cell Formulas
Range	Formula
K2:K15	K2	=TEXTAFTER(J2," ",,,,J2)

 

[ChetanPuri]

=TEXTAFTER(J2," ",,,,J2

Maybe your sample data is not representative enough but for that data you could try this.

24 03 07.xlsm
	J	K
1	Pay Rate Level
2	SALARY	SALARY
3	SALARY	SALARY
4	SALARY	SALARY
5	SALARY	SALARY
6	SALARY	SALARY
7	SALARY	SALARY
8	SCHADS L5.3	L5.3
9	SCHADS L6.3	L6.3
10
11	SALARY	SALARY
12	SALARY	SALARY
13	SCHADS L6.3	L6.3
14	SCHADS L5.2	L5.2
15	SCHADS L6.2	L6.2
ChetanPuri

Cell Formulas
Range	Formula
K2:K15	K2	=TEXTAFTER(J2," ",,,,J2)

Thank you so much, Is there a possibility, I don't get text after .3 for example only L5 or L4 etc. Many thanks

 

[Rick Rothstein]

Maybe this...

=TEXTBEFORE(TEXTAFTER(J13," ",-1,,,J13)&".",".")

Edit Note: I modified my original formula by adding the -1 which would allow the formula to still work even if the beginning text had spaces in it.

 

[Peter_SSs]

Another possible option

24 03 07.xlsm
	J	K
1	Pay Rate Level
2	SALARY	SALARY
3	SALARY	SALARY
4	SALARY	SALARY
5	SALARY	SALARY
6	SALARY	SALARY
7	SALARY	SALARY
8	SCHADS L5.3	L5
9	SCHADS L6.3	L6
10
11	SALARY	SALARY
12	SALARY	SALARY
13	SCHADS L6.3	L6
14	SCHADS L5.2	L5
15	SCHADS L6.2	L6
ChetanPuri

Cell Formulas
Range	Formula
K2:K15	K2	=INDEX(TEXTSPLIT(J2&" "&J2&".",{" ","."}),2)

 

[ChetanPuri]

Maybe this...

=TEXTBEFORE(TEXTAFTER(J13," ",-1,,,J13)&".",".")

Edit Note: I modified my original formula by adding the -1 which would allow the formula to still work even if the beginning text had spaces in it.

Thank you so much

 

[ChetanPuri]

Another possible option

24 03 07.xlsm
	J	K
1	Pay Rate Level
2	SALARY	SALARY
3	SALARY	SALARY
4	SALARY	SALARY
5	SALARY	SALARY
6	SALARY	SALARY
7	SALARY	SALARY
8	SCHADS L5.3	L5
9	SCHADS L6.3	L6
10
11	SALARY	SALARY
12	SALARY	SALARY
13	SCHADS L6.3	L6
14	SCHADS L5.2	L5
15	SCHADS L6.2	L6
ChetanPuri

Cell Formulas
Range	Formula
K2:K15	K2	=INDEX(TEXTSPLIT(J2&" "&J2&".",{" ","."}),2)

Thanks a lot

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.