Formula to extract characters from text string

[helphelp]

Hi mrexcel forum,

Could you please suggest a formula to help me with this issue?
Issue A: I will like to extract out the numbers immediately before and after the dot. This is the volume information.
Issue B: I will like to extract the 3 characters immediately after the Volume information.

Example 1: AB BLACK SEDUCTION 3.4 EDT M (100111)
Issue A solution: Shows 3.4
Issue B solution: Shows EDT

Example 2: PACO OLYMPEA INTENSE 1.7 EDP L (108709)
Issue A solution: Shows 1.7
Issue B solution: Shows EDP

Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

Both your examples just have a single digit before/after the dot and 3-characters after that to extract for issue B. If that is true for all data, then try these formulas. If you need column B as a number, not text then add +0 to the end of the formula.
If it is not true for all then could we have a few more examples that show the full variety of what you have and what you want?

Excel Workbook
	A	B	C
1	AB BLACK SEDUCTION 3.4 EDT M (100111)	3.4	EDT
2	PACO OLYMPEA INTENSE 1.7 EDP L (108709)	1.7	EDP
Extract Bits

 

[helphelp]

Welcome to the MrExcel board!

Both your examples just have a single digit before/after the dot and 3-characters after that to extract for issue B. If that is true for all data, then try these formulas. If you need column B as a number, not text then add +0 to the end of the formula.
If it is not true for all then could we have a few more examples that show the full variety of what you have and what you want?

Extract Bits

	A	B	C
AB BLACK SEDUCTION 3.4 EDT M (100111)	EDT
PACO OLYMPEA INTENSE 1.7 EDP L (108709)	EDP

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:327px;"><col style="width:87px;"><col style="width:78px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]3.4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]1.7[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =MID(A1,FIND(".",A1)-1,3) C1 =MID(A1,FIND(".",A1)+3,3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks!

 

[Peter_SSs]

Thanks!

You're welcome.

Actually, if the final part of the strings after what you are searching for are space-single character-space-6 digit number in parentheses like you samples, you could also use these formulas.

Excel Workbook
	A	B	C
1	AB BLACK SEDUCTION 3.4 EDT M (100111)	3.4	EDT
2	PACO OLYMPEA INTENSE 1.7 EDP L (108709)	1.7	EDP
Extract Bits

 

[helphelp]

You're welcome.

Actually, if the final part of the strings after what you are searching for are space-single character-space-6 digit number in parentheses like you samples, you could also use these formulas.

Extract Bits

	A	B	C
AB BLACK SEDUCTION 3.4 EDT M (100111)	EDT
PACO OLYMPEA INTENSE 1.7 EDP L (108709)	EDP

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:327px;"><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]3.4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]1.7[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =LEFT(RIGHT(A1,18),3) C1 =LEFT(RIGHT(A1,14),3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you. Sorry to trouble you again. Unfortunately, some of the names look like this.

CK ALL 15 ML EDT L (110202)
DIOR JADORE 15ML EDP L REFILLABLE (111482)
DIOR JADORE 15OZ EDP L REFILLABLE (111482)
DIOR JADORE 15 OZ EDP L REFILLABLE (111482)

Is it possible to incorporate a formula to extract out the 15 which appears before the ML and OZ?

Thanks.

 

[Peter_SSs]

This data looks quite different to the original.

1. If both sorts of values can exist, can you tell us in words how you would manually decide how to locate the numbers to extract and how to decide which letters to extract? There has to some logic to apply or else we can't tell excel what to do.

2. Is the number to extract always the only number in the string apart from in parentheses at the end? Or could there be other digits like "A1 BLACK SEDUCTION 3.4 EDT M (100111)"

3. How many different units of measurement can there be? (Your examples include "ML" and "OZ")

4. Are the texts always in upper case like your examples so far?

5. If a formula solution is not feasible, would a macro solution be acceptable?