How to extract a substring from a string?

[rca]

I have the following string in cell A1:

Color=red;Length=5;Description=fast,Depth=10;Width=3

The numerical values in the above-mentioned string can be any number of digits (but, practically speaking, limited to 7 digits).

For example, the string could just as easily be:

Color=red;Length=350;Description=fast,Depth=4;Width=19

The following formula allows me to extract the value for "Depth" if "Depth" is a single digit:

=MID(A1,FIND("Depth=",A1)+LEN("Depth="),1)

If "Depth" were 2 digits, then this formula would only return the first digit (because the value of LEN() is set to 1).

Without using Text to Columns, how would I extract the value of "Depth" if its value could be any number of digits?

Thanks!

 

[Scott T]

IF they are always in that order, Width after depth with a ; in between this should work

=MID(A1,SEARCH("depth=",A1)+6,SEARCH(";width=",MID(A1,SEARCH("depth=",A1)+6,999))-1)

 

[rca]

@Scott T

That solution works if Width is after Depth in the string. This is the case most of the time, but not all of the time. Also, there are many other "keys" in the dictionary which may or may not be present in the string. For example, the string could read:

Color=red;Length=350;Description=fast,Width=22;Volume=29;Depth=8

(introducing a variable between "Width" and "Depth")

Any ideas how to work around that? (without having to nest a lot of IF statements and test for the presence of the semi-colon in the result)

 

[Scott Huish]

=MID(A1,(SEARCH("depth=",A1)+6),(SEARCH(";",A1&";",(SEARCH("depth=",A1)+6)))-(SEARCH("depth=",A1)+6))

Excel 2010
	A	B	C
1	Color=red;Length=350;Description=fast,Width=22;Volume=29;Depth=8		8
2	Color=red;Length=5;Description=fast,Depth=10;Width=3		10
Sheet1

Cell Formulas
Range	Formula
C1	=MID(A1,(SEARCH("depth=",A1)+6),(SEARCH(";",A1&";",(SEARCH("depth=",A1)+6)))-(SEARCH("depth=",A1)+6))
C2	=MID(A2,(SEARCH("depth=",A2)+6),(SEARCH(";",A2&";",(SEARCH("depth=",A2)+6)))-(SEARCH("depth=",A2)+6))

 

[Scott T]

If there is always a ; after the depth number or depth is at the end this will work.

Excel 2010
	A	B
1	Color=red;Length=5;Description=fast,Depth=10;Width=3	10
2	Color=red;Length=350;Description=fast,Depth=4;Width=19	4
3	Color=red;Length=350;Description=fast,Width=22;Volume=29;Depth=8	8
4	Color=red;Length=30;Description=fast,Depth=664;Width=19	664
5	Color=red;Length=350;Description=fast,Width=22;Volume=29;Depth=88	88
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",999)),SEARCH("depth=",SUBSTITUTE(A1,";",REPT(" ",999)))+6,999))+0
B2	=TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",999)),SEARCH("depth=",SUBSTITUTE(A2,";",REPT(" ",999)))+6,999))+0
B3	=TRIM(MID(SUBSTITUTE(A3,";",REPT(" ",999)),SEARCH("depth=",SUBSTITUTE(A3,";",REPT(" ",999)))+6,999))+0
B4	=TRIM(MID(SUBSTITUTE(A4,";",REPT(" ",999)),SEARCH("depth=",SUBSTITUTE(A4,";",REPT(" ",999)))+6,999))+0
B5	=TRIM(MID(SUBSTITUTE(A5,";",REPT(" ",999)),SEARCH("depth=",SUBSTITUTE(A5,";",REPT(" ",999)))+6,999))+0

 

[Rick Rothstein]

Here is another formula for you to try (change the red highlighted text to get the number after different keywords)...

=TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,"Depth=",REPT(" ",99)),99,99)),";",REPT(" ",99)),1,99))

It returns the number as text... if you want it to be a real number, just add zero to the formula...

=0+TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,"Depth=",REPT(" ",99)),99,99)),";",REPT(" ",99)),1,99))