Outputting numbers extracted from a string

[thinksmart12]

Hey everyone,
I am trying to create a function that will extract a number in the given string and print that number in the cell. I had this issue in the past and found the solution on these forums using either LOOKUP or VLOOKUP, but stupid me cannot find that post again.

I have data that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl67, width: 569"]Lab 5 because seeing the titration first hand helped me to understand the equivalence point.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]Experiment 12 because it gave me a huge understanding of how to use the gas laws and apply it to real situations.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]Lab 10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl67, width: 569"]The last lab report, lab 12 required me to use a lot of things that we've learned throughout the semester and really made some things make more sense.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]Lab 10: Qualitative Determination of Selected Metal Cations. This lab supported the course content because it gave me a "real world" relation to what was being studied.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][TABLE="width: 569"]
<tbody>[TR]
[TD="class: xl66, width: 569"]The calorimetry lab really helped my understanding of the use of the different equations and the types of problems I would encounter on the test.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

What I want to first accomplish, is to put some formula and drag it down in column B, so that B gets populated with the numbers from column A. I have hundreds of rows where there is some number given in column A. I would like to drag down over the hundreds of rows and have it output the corresponding number from column A into column B.

If you look at the last row in italics, there is no number in column A. For these rows, I would like to have a formula that searches for certain keywords (in this case, the word "calorimetry") and output a number based on that. If the keyword is not present, then skip it. I can then modify this formula to search for different words and output different numbers based on that.

If anyone can help with this, that would be great! Like I said, I think the solution to this was posted in this forum before, but after extensive searching I cannot find it.

Thank you!

 

[Ron Coderre]

This isn't particularly elegant...but...
With your sample data in A1:A5
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]

Lab 5 because seeing the titration first hand helped me to understand the equivalence point.​

[/td][/tr]

[tr][td]

2​

[/td][td]

Experiment 12 because it gave me a huge understanding of how to use the gas laws and apply it to real situations.​

[/td][/tr]

[tr][td]

3​

[/td][td]

Lab 10: Qualitative Determination of Selected Metal Cations. This lab supported the course content because it gave me a "real world" relation to what was being studied.​

[/td][/tr]

[tr][td]

4​

[/td][td]

The last lab report, lab 12 required me to use a lot of things that we've learned throughout the semester and really made some things make more sense.​

[/td][/tr]

[tr][td]

5​

[/td][td]

Lab 10: Qualitative Determination of Selected Metal Cations. This lab supported the course content because it gave me a "real world" relation to what was being studied.​

[/td][/tr]

[tr][td]

6​

[/td][td]

The calorimetry lab really helped my understanding of the use of the different equations and the types of problems I would encounter on the test.​

[/td][/tr]
[/table]

and this lookup table:
[Table="class: grid"][tr][td] [/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]

Text​

[/td][td]

Value​

[/td][/tr]

[tr][td]

2​

[/td][td]

Calorimetry​

[/td][td]

4​

[/td][/tr]

[tr][td]

3​

[/td][td]

Kniffle Pin​

[/td][td]

1​

[/td][/tr]

[tr][td]

4​

[/td][td]

Snucky​

[/td][td]

2​

[/td][/tr]
[/table]

This formula, copied down, returns the embedded number from the text. If no number is found, the formula tries to find the lookup text candidates in the text and returns an assigned value

B1: =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) < LEN(A1)
,LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))
,IFERROR(INDEX($G$2:$G$4,MATCH(1,INDEX(COUNTIF(A1,"*"&$F$2:$F$4&"*"),0),0)),0))

Is that something you can work with?

 

[thinksmart12]

Yes, this is perfect - you are a life saver!

This isn't particularly elegant...but...
With your sample data in A1:A5
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Lab 5 because seeing the titration first hand helped me to understand the equivalence point.​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Experiment 12 because it gave me a huge understanding of how to use the gas laws and apply it to real situations.​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Lab 10: Qualitative Determination of Selected Metal Cations. This lab supported the course content because it gave me a "real world" relation to what was being studied.​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

The last lab report, lab 12 required me to use a lot of things that we've learned throughout the semester and really made some things make more sense.​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Lab 10: Qualitative Determination of Selected Metal Cations. This lab supported the course content because it gave me a "real world" relation to what was being studied.​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

The calorimetry lab really helped my understanding of the use of the different equations and the types of problems I would encounter on the test.​

[/TD]
[/TR]
</tbody>[/TABLE]

and this lookup table:
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Text​

[/TD]
[TD]

Value​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Calorimetry​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Kniffle Pin​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Snucky​

[/TD]
[TD]

2​

[/TD]
[/TR]
</tbody>[/TABLE]

This formula, copied down, returns the embedded number from the text. If no number is found, the formula tries to find the lookup text candidates in the text and returns an assigned value

B1: =IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) < LEN(A1)
,LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))
,IFERROR(INDEX($G$2:$G$4,MATCH(1,INDEX(COUNTIF(A1,"*"&$F$2:$F$4&"*"),0),0)),0))

Is that something you can work with?

 

[bosco_yip]

Another option in using Ron's Lookup Table F2:G4 in post# 2 :

=IFERROR(-LOOKUP(,-(MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1/19)),ROW($1:$20))&"**0")),IFERROR(LOOKUP(1,-SEARCH(F$2:F$4,A1),G$2:G$4),""))

Regards