Extract a specific value between texts in a cell

[barracuda24]

Hello,

We import a report from a website and several of the key information get put into one cell which is very annoying. The problem is the number of characters differs in each cell.

With the example below, what formula can I use to get the ":No of Delegates:" values in column B, which would be 20 and 5.


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 936"]
<tbody>[TR]
[TD="class: xl60, width: 936"]Sandwiches please... Subject of Meeting: Group 1 Meeting... No of Delegates: 20... Room Layout: Conference... Refreshments: Tea/Coffee/Water Biscuits... Lunch: Other[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][TABLE="width: 936"]
<tbody>[TR]
[TD="class: xl58, width: 936"]Subject of Meeting: Training Course Planning Day... No of Delegates: 5... Refreshments: Not Required... Lunch: Not Required"

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

Many thanks for the assistance!

 

[Joe4]

Re: How to extract a specific value between texts in a cell

Welcome to the Board!

Is there always exactly 3 dots after the number?, i.e.
20...
5...

 

[JoeMo]

Re: How to extract a specific value between texts in a cell

If the number of delegates will never exceed 99, try this:

Excel Workbook
	A	B
1	Sandwiches please... Subject of Meeting: Group 1 Meeting... No of Delegates: 20... Room Layout: Conference... Refreshments: Tea/Coffee/Water Biscuits... Lunch: Other	20
2	Subject of Meeting: Training Course Planning Day... No of Delegates: 5... Refreshments: Not Required... Lunch: Not Required"	5
Sheet1

 

[barracuda24]

Re: How to extract a specific value between texts in a cell

Yes that seems to be the pattern, 3 '...' following the delegates

 

[barracuda24]

Re: How to extract a specific value between texts in a cell

Spreadsheet Formulas

Cell Formula B1 =SUBSTITUTE(MID(A1,SEARCH("Delegates: ",A1)+LEN("Delegates: "),5),".","") <tbody> </tbody>

<tbody>
</tbody>

Thank you! It seems to be working now. If it isn't too much trouble, could you explain what the '5' in the formula stand for so I can use this for other fields. Thank you very much once again.

 

[JoeMo]

Re: How to extract a specific value between texts in a cell

Spreadsheet Formulas

Cell Formula B1 =SUBSTITUTE(MID(A1,SEARCH("Delegates: ",A1)+LEN("Delegates: "),5),".","") <tbody> </tbody>

<tbody>
</tbody>

Thank you! It seems to be working now. If it isn't too much trouble, could you explain what the '5' in the formula stand for so I can use this for other fields. Thank you very much once again.

You are welcome.

The 5 is the length of the string that follows "Delegates: " to be extracted. That's up to two digits and the three "..." dots following those digits.

That extracts a string, but if you want to extract actual numbers try this:

=TRIM(SUBSTITUTE(MID(A1,SEARCH("Delegates: ",A1)+LEN("Delegates: "),5),".",""))+0