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]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		After Nth Space					Second Situation
2		1	2	3	4			1	2	3
3	Chl_Cpick 4 Chl_Mar 6	Chl_Cpick	4	Chl_Mar	6		Chl_Cpick 4Chl_Mar 6	Chl_Cpick	4	Chl_Mar	6
4	CrM_CLN 4 Chl_C/B 4	CrM_CLN	4	Chl_C/B	4		CrM_CLN 4Chl_C/B 4	CrM_CLN	4	Chl_C/B	4
5	ABS 4 Chl_C/B 4	ABS	4	Chl_C/B	4		ABS 4Chl_C/B 4	ABS	4	Chl_C/B	4
6	CrM_CLN 4 Hol 4	CrM_CLN	4	Hol	4		CrM_CLN 4Hol 4	CrM_CLN	4	Hol	4
7
8
Sheet1

Cell Formulas
Range	Formula
H3:H6,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)))
I3:I6	I3	=MID($G3,FIND("~",SUBSTITUTE($G3," ","~",H$2))+1,1)
J3:J6	J3	=MID($G3,FIND("~",SUBSTITUTE($G3," ","~",H$2))+2,FIND("~",SUBSTITUTE($G3," ","~",I$2))-FIND("~",SUBSTITUTE($G3," ","~",H$2))-2)
K3:K6	K3	=RIGHT($G3,LEN($G3)-FIND("~",SUBSTITUTE($G3," ","~",I$2)))

 

[Lee85]

I see the Space before Numbers, Is it correct?

Yes the space before the numbers is correct
but after the 1st number there is no space
How would i go about by splitting them in that case
And how would i combine it with your 1st code

so if i had some appear like
Chl_Cpick 4Chl_Mar 6
CrM_CLN 8


sorry if i did'nt make it clear i am new to all this

Thanks for your help though

 

[Lee85]

just seen your second situation which works perfect for what i need

thank you so much

 

[maabadi]

You're Welcome & Thanks for Feedback.

 

[Lee85]

One thing i totally forgot about is some of the numbers will be double so i could have
Chl_Cpick 2Chl_Mar 10 or just Chl_Mar 10 depending on what the day is your code works great if there single numbers but can it be adapted if they are are double or single

Thanks again

 

[maabadi]

At the first situation I change formula to Separated Both double number

NewRun.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		After Nth Space					Second Situation
2		1	2	3	4			1	2	3
3	Chl_Cpick 10 Chl_Mar 10	Chl_Cpick	10	Chl_Mar	10		Chl_Cpick 4Chl_Mar 6	Chl_Cpick	4	Chl_Mar	6
4	CrM_CLN 4 Chl_C/B 4	CrM_CLN	4	Chl_C/B	4		CrM_CLN 4Chl_C/B 10	CrM_CLN	4	Chl_C/B	10
5	ABS 10 Chl_C/B 4	ABS	10	Chl_C/B	4		ABS 4Chl_C/B 4	ABS	4	Chl_C/B	4
6	CrM_CLN 10 Hol 4	CrM_CLN	10	Hol	4		CrM_CLN 4Hol 4	CrM_CLN	4	Hol	4
Sheet1

Cell Formulas
Range	Formula
H3:H6,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	=TRIM(RIGHT(A3,LEN(A3)-FIND("~",SUBSTITUTE($A3," ","~",D$2))+1))
I3:I6	I3	=MID($G3,FIND("~",SUBSTITUTE($G3," ","~",H$2))+1,1)
J3:J6	J3	=MID($G3,FIND("~",SUBSTITUTE($G3," ","~",H$2))+2,FIND("~",SUBSTITUTE($G3," ","~",I$2))-FIND("~",SUBSTITUTE($G3," ","~",H$2))-2)
K3:K6	K3	=TRIM(RIGHT($G3,LEN($G3)-FIND("~",SUBSTITUTE($G3," ","~",I$2))+1))

 

[Lee85]

What if I just had CrM_CLN 10 along with the other mixed ones how would your formula work in this way both ways work when there is two sections to look at but can it be adapted to also look for the odd ones that would only have CrM_CLN 10 or something similar in the cell

	After Nth Space				Second Situation
	1​	2​	3​	4​			1​	2​	3​
Chl_Cpick 10 Chl_Mar 10	Chl_Cpick	10	Chl_Mar	10		Chl_Cpick 4Chl_Mar 6	Chl_Cpick	4	Chl_Mar	6
CrM_CLN 4 Chl_C/B 4	CrM_CLN	4	Chl_C/B	4		CrM_CLN 4Chl_C/B 10	CrM_CLN	4	Chl_C/B	10
ABS 10 Chl_C/B 4	ABS	10	Chl_C/B	4		ABS 4Chl_C/B 4	ABS	4	Chl_C/B	4
CrM_CLN 10	CrM_CLN	#VALUE!​	#VALUE!​	#VALUE!​		CrM_CLN 10	CrM_CLN	1	#VALUE!​	#VALUE!​

Thanks for your help on this

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		After Nth Space					Second Situation
2		1	2	3	4			1	2	3
3	Chl_Cpick 10 Chl_Mar 10	Chl_Cpick	10	Chl_Mar	10		Chl_Cpick 4Chl_Mar 6	Chl_Cpick	4	Chl_Mar	6
4	CrM_CLN 4 Chl_C/B 4	CrM_CLN	4	Chl_C/B	4		CrM_CLN 4Chl_C/B 10	CrM_CLN	4	Chl_C/B	10
5	ABS 10 Chl_C/B 4	ABS	10	Chl_C/B	4		ABS 4Chl_C/B 4	ABS	4	Chl_C/B	4
6	CrM_CLN 10	CrM_CLN			10		CrM_CLN 4Hol 4	CrM_CLN	4	Hol	4
7
Sheet1

Cell Formulas
Range	Formula
H3:H6,B3:B6	B3	=LEFT(A3,FIND(" ",A3,1)-1)
C3:D6	C3	=IFERROR(MID($A3,FIND("~",SUBSTITUTE($A3," ","~",B$2))+1,FIND("~",SUBSTITUTE($A3," ","~",C$2))-FIND("~",SUBSTITUTE($A3," ","~",B$2))-1),"")
E3:E5	E3	=TRIM(RIGHT(A3,LEN(A3)-FIND("~",SUBSTITUTE($A3," ","~",D$2))+1))
E6	E6	=TRIM(RIGHT(A6,LEN(A6)-(LEN(B6)+IFERROR(LEN(C6),0)+IFERROR(LEN(D6),0))))
I3:I6	I3	=MID($G3,FIND("~",SUBSTITUTE($G3," ","~",H$2))+1,1)
J3:J6	J3	=MID($G3,FIND("~",SUBSTITUTE($G3," ","~",H$2))+2,FIND("~",SUBSTITUTE($G3," ","~",I$2))-FIND("~",SUBSTITUTE($G3," ","~",H$2))-2)
K3:K6	K3	=TRIM(RIGHT($G3,LEN($G3)-FIND("~",SUBSTITUTE($G3," ","~",I$2))+1))

 

[Lee85]

Hi Maabadi

This really helped alot but i now have another issue

We now get some that come through as 3.5 or something similar how could your code be adapted to caputre this as well
and is there a way to hide the #values

Book1.xls
	A	B	C	D	E
1	CrE_CRec 10	CrE_CRec	10	#VALUE!	######
2	CrE_CRec 4 CrE_CLN 6	CrE_CRec	4	CrE_CLN	6
3	CrE_CRec 4.5 CrE_CLN 5.5	CrE_CRec	4.	.5 CrE_CLN	5.5
Sheet1

hope this make sense

Regards

Lee

 

[maabadi]

This is very Complicated. You can Use UDF that I Created For you. If you has familiar with VBA it is very Easy. If Not Please Do Struction Step to Step.
1. At Excel Window Press ALT+F11 (this Open VBA window).
2. Go to Insert Menu & Select Module (Shortcut is ALT+I then ALt+M)
3. Then Paste this code at that windows appears:

Public Function SplitText(pWorkRng As Range, pIsNumber As Long) As String
Dim xLen As Long, xStr1 As String, xStr2 As String, K As Long, i As Long
Dim Prt1 As String, Prt2 As String, Prt3 As String, Prt4 As String
xLen = Len(pWorkRng.Value)
xStr1 = VBA.Mid(pWorkRng.Value, 1, 1)
SplitText = xStr1
K = 1
For i = 2 To xLen
    If i > 2 Then xStr1 = VBA.Mid(pWorkRng.Value, i - 1, 1)
     xStr2 = VBA.Mid(pWorkRng.Value, i, 1)
    If VBA.IsNumeric(xStr2) = VBA.IsNumeric(xStr1) Or xStr2 = "." Or xStr1 = "." Then
        SplitText = SplitText + xStr2
    Else
      Select Case K
       Case 1
       Prt1 = SplitText
       Case 2
       Prt2 = SplitText
       Case 3
       Prt3 = SplitText
      
     End Select
       K = K + 1
       SplitText = VBA.Mid(pWorkRng.Value, i, 1)
    End If
Next i
Prt4 = SplitText
Select Case pIsNumber
       Case 1
       SplitText = Trim(Prt1)
       Case 2
       SplitText = Trim(Prt2)
       Case 3
       SplitText = Trim(Prt3)
       Case 4
       SplitText = Trim(Prt4)
     End Select
End Function

4. Close VBA window and then Save as Your file as Macro-Enabled Workbook (.xlsm)
5. Now your function is ready.
6. input in each cell =SplitText(
7.Select your Source Cell or type address for fist item
8. Input Part number that you want extract (from 1 to 4) Based your Data
9. Close Pranthesis and Press Enter

Example:

=SplitText(A3,1)

OR

=SplitText(A3,2)