Extract numerical value after certain text

[Lee85]

• #1

Hi

I am kinda new to this and I am hoping someone can help me
I would like to extract a numerical value after specific text
Example
Green 6 red 4
Green 8 blue 3
How would Ij extract just the 8 when the specific is Green and also is the word was Green and red

Hope this makes sense

 

[maabadi]

Welcome to MrExcel Message Board.
1. Are you want Only Extract 8 Or All numbers comes after Green?
2. For Green & Red, Example you want Extract only 6, OR 6 & 4 Separately at two cell OR Extract 64?

 

[maabadi]

Example

Book1
	A	B	C	D
1		Green	Green & Red
2	green 6 Red 4	6	6, 4
3	Green 8 Blue 3	8	8
4	Green 7	7	7
5	Blue 3 green 5	5	5
6	Blue 5
7
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(TRIM(MID(A2,SEARCH("Green",A2,1)+5,2)),"")
C2:C6	C2	=IFERROR(TRIM(MID(A2,SEARCH("Green",A2,1)+5,2)),"")&IFERROR(", "&TRIM(MID(A2,SEARCH("Red",A2,1)+3,2)),"")

 

[Lee85]

Hi thanks for this it helped a lot
Now i am wondering how i would i go about changing this formula to split the below while look at my list as i only want the info that is in my list

Chl_CPick 4 Chl_Mar 6

CrM_CLN 4 Chl_C/B 4

ABS 4 Chl_C/B 4

CrM_CLN 4 Hol 4

my list is the following

Chl_CPick

Chl_Mar

Chl_Qd

Chl_Hyg

Chl_C/B

Chl_Train

Chl_Other

Chl_Meet

Chl_Nag1

Chl_Nag2

Chl_Nag3

Chl_Nag4

Chl_Nag5

CrE_CRec

CrM_CRec

CrN_CRec

CrE_CLN

CrE_CLS

CrM_CLN

CrM_CLS

CrN_CLN

CrN_CLS

Any help on this would be much appreciated

 

[KP117]

Data at A1 and down (2 lines in each cell), the list at B1 and down, consider this formula at C1 and copy down,
=IFERROR(TRANSPOSE(SORT(TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",10)),LEN($B$1:$B$22)+FIND($B$1:$B$22,SUBSTITUTE($A1,CHAR(10),REPT(" ",10))),10)))),"")

 

[Lee85]

=IFERROR(TRANSPOSE(SORT(TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",10)),LEN($B$1:$B$22)+FIND($B$1:$B$22,SUBSTITUTE($A1,CHAR(10),REPT(" ",10))),10)))),"")

Keeps coming up That function is not valid and then highlights the sort value

 

[maabadi]

Please upload your example file with XL2BB ADDIN at above of reply section OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[maabadi]

Try this:

NewRun.xlsm
	A	B	C	D	E	F
1		After Nth Space
2		1	2	3	4
3	Chl_Cpick 4 Chl_Mar 6	Chl_Cpick	4	Chl_Mar	6
4	CrM_CLN 4 Chl_C/B 4	CrM_CLN	4	Chl_C/B	4
5	ABS 4 Chl_C/B 4	ABS	4	Chl_C/B	4
6	CrM_CLN 4 Hol 4	CrM_CLN	4	Hol	4
7
Sheet3

Cell Formulas
Range	Formula
B3:B6	B3	=LEFT(A3,FIND(" ",A3,1)-1)
C3:D6	C3	=MID($A3,FIND("~",SUBSTITUTE($A3," ","~",B$2))+1,FIND("~",SUBSTITUTE($A3," ","~",C$2))-FIND("~",SUBSTITUTE($A3," ","~",B$2))-1)
E3:E6	E3	=RIGHT(A3,LEN(A3)-FIND("~",SUBSTITUTE($A3," ","~",D$2)))

 

[Lee85]

This works like a charm thank you
I also get a few come through on my main sheet with no space
So they come through like below
Chl_Cpick 4Chl_Mar 6
How would I separate them

 

[maabadi]

I see the Space before Numbers, Is it correct?