Extracting out specific text from within a cell

[helphelp]

Hi all, I am trying to extract out various details from a text string.

The fields I am trying to extract are as follows:
Field 1 = volume information eg. 10
Field 2 = volume measurement eg. Oz or ML
Field 3 = perfume type eg. EDT, Eau De Toilette. On this field specifically, several equivalent terms are used so I am trying to standardize them eg. if Eau De Toilette is shown then EDT should be the text string that is shown. Or if Eau De Parfum is shown then EDP should be shown. Or if Eau De Cologne is shown then EDC should be the text string that is shown.

I have managed to solve Field 2 by using this formula. =IF(ISNUMBER(SEARCH("OZ",C2))="true","ML","OZ").

Can someone help me out with Field1 and Field 3? I will give a few examples on the intended results. Thank you

Example 1: 1 oz Eau De Toilette Spray
Field 1 result: 1
Field 2 result: oz
Field 3 result: EDT

Example 2: 1.6 oz EDT Spray
Field 1 result: 1.6
Field 2 result: oz
Field 3 result: EDT

Example 3: 3.4 ml Eau De Cologne Concentre Spray
Field 1 result: 3.4
Field 2 result: ml
Field 3 result EDC

Example 4: 3.4 oz Eau De Parfum Spray (Unisex)
Field 1 result: 3.4
Field 2 result: oz
Field 3 result: EDP

Example 5: 2.8 oz EDP Spray (Limited Edition)
Field 1 result: 2.8
Field 2 result: oz
Field 3 result: EDP

 

[Jborg]

For Field 3 Eau De Toilette Spray

Put your Text in Cell A1
and then but the formula below in Cell B1. I know it is a huge formula, but I'm sure that you will find an expert that will shorten it for you. However it works


=TEXTJOIN("",,MID(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""),MATCH(1,(1-(MID(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),"")))),1)=" "))*(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""))))),0),1),MID(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""),MATCH(0,(1-(MID(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),"")))),1)=" "))*(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""))))),0)+1,1),MID(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""),MATCH(2,1/--((1-(MID(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),"")))),1)=" "))*(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,MID(A1,MATCH(2,1/--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")),100),"")))))=0))+1,1))

 

[Jborg]

I managed to shorten it a bit:
Put this in B1 and it should work aswell:
=TEXTJOIN("",,MID(A1,SEARCH("",A1,1),1),MID(A1,SEARCH(" ",A1,1)+1,1),MID(A1,SMALL(MODE.MULT(IFNA(MATCH(ROW(INDIRECT("1:"&LEN(A1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*(ROW(INDIRECT("1:"&LEN(A1)))),{0,0}),"")),2)+1,1))

 

[mohadin]

HI
Try this

Sub test()
    Application.ScreenUpdating = False
    Dim a As Variant, m, i, lr, t, arr, o1
    o1 = "!@#"
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    ReDim a(1 To lr)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\s[a-z]{2})|(\d.?.)|([A-Z])"
        For t = 1 To lr
            If .test(Cells(t, 1)) Then
                Set m = .Execute(Cells(t, 1))
                Cells(t, 2) = m(0)
                Cells(t, 3) = m(1)
                Cells(t, 4) = m(2) & m(3) & m(4)
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Only if you can change the entry of 1 to 1.0 Sorry for that

 

[DanteAmor]

Using a search table:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:320.32px;" /><col style="width:71.29px;" /><col style="width:71.29px;" /><col style="width:96.95px;" /><col style="width:23.76px;" /><col style="width:23.76px;" /><col style="width:23.76px;" /><col style="width:150.18px;" /><col style="width:89.35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">EXAMPLE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">FIELD 1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">FIELD 2</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">FIELD 3</td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">TERMS</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">EQUIVALENCE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >1 oz Eau De Toilette Spray</td><td style="text-align:right; ">1</td><td >oz</td><td >EDT</td><td > </td><td > </td><td > </td><td >Eau De Toilette</td><td >EDT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >1.6 oz EDT Spray</td><td style="text-align:right; ">1.6</td><td >oz</td><td >EDT</td><td > </td><td > </td><td > </td><td >EDT</td><td >EDT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >3.4 ml Eau De Cologne Concentre Spray</td><td style="text-align:right; ">3.4</td><td >ml</td><td >EDC</td><td > </td><td > </td><td > </td><td >Eau De Cologne</td><td >EDC</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >3.4 oz Eau De Parfum Spray (Unisex)</td><td style="text-align:right; ">3.4</td><td >oz</td><td >EDP</td><td > </td><td > </td><td > </td><td >EDC</td><td >EDC</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >2.8 oz EDP Spray (Limited Edition)</td><td style="text-align:right; ">2.8</td><td >oz</td><td >EDP</td><td > </td><td > </td><td > </td><td >Eau De Parfum</td><td >EDP</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >1 Oz EDC Spray</td><td style="text-align:right; ">1</td><td >Oz</td><td >EDC</td><td > </td><td > </td><td > </td><td >EDP</td><td >EDP</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=MID(SUBSTITUTE(" " & $A2," ",REPT(" ",99)),99*COLUMNS($B$1:B1),99)+0</td></tr><tr><td >C2</td><td >=TRIM(MID(SUBSTITUTE(" " & $A2," ",REPT(" ",99)),99*COLUMNS($B$1:C1),99))</td></tr><tr><td >D2</td><td >=IFERROR(LOOKUP(9.99E+307,SEARCH(H$2:H$7,A2),I$2:I$7),"")</td></tr></table></td></tr></table>

 

[sandy666]

with your example you can try PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]RAW[/td][td][/td][td=bgcolor:#70AD47]RAW.1[/td][td=bgcolor:#70AD47]RAW.2.1[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1 oz Eau De Toilette Spray[/td][td][/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]oz[/td][td=bgcolor:#E2EFDA]EDT[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1.6 oz EDT Spray[/td][td][/td][td]1.6[/td][td]oz[/td][td]EDT[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]3.4 ml Eau De Cologne Concentre Spray[/td][td][/td][td=bgcolor:#E2EFDA]3.4[/td][td=bgcolor:#E2EFDA]ml[/td][td=bgcolor:#E2EFDA]EDC[/td][/tr]

[tr=bgcolor:#FFFFFF][td]3.4 oz Eau De Parfum Spray (Unisex)[/td][td][/td][td]3.4[/td][td]oz[/td][td]EDP[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]2.8 oz EDP Spray (Limited Edition)[/td][td][/td][td=bgcolor:#E2EFDA]2.8[/td][td=bgcolor:#E2EFDA]oz[/td][td=bgcolor:#E2EFDA]EDP[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split1 = Table.SplitColumn(Source, "RAW", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"RAW.1", "RAW.2"}),
    Split2 = Table.SplitColumn(Split1, "RAW.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"RAW.2.1", "RAW.2.2"}),
    Split3 = Table.SplitColumn(Split2, "RAW.2.2", Splitter.SplitTextByAnyDelimiter({" Spray"," Concentre"}, QuoteStyle.Csv)),
    Conditions = Table.AddColumn(Split3, "Custom", each if [RAW.2.2.1] = "Eau De Toilette" then "EDT" else if [RAW.2.2.1] = "Eau De Cologne" then "EDC" else if [RAW.2.2.1] = "Eau De Parfum" then "EDP" else [RAW.2.2.1]),
    ROC = Table.SelectColumns(Conditions,{"RAW.1", "RAW.2.1", "Custom"})
in
    ROC[/SIZE]

Edit: FYI this is NOT vba

 

[helphelp]

Thanks to everyone who replied to this thread.

Using a search table:

	A	B	C	D	E	F	G	H	I
1 oz Eau De Toilette Spray	oz	EDT				Eau De Toilette	EDT
1.6 oz EDT Spray	oz	EDT				EDT	EDT
3.4 ml Eau De Cologne Concentre Spray	ml	EDC				Eau De Cologne	EDC
3.4 oz Eau De Parfum Spray (Unisex)	oz	EDP				EDC	EDC
2.8 oz EDP Spray (Limited Edition)	oz	EDP				Eau De Parfum	EDP
1 Oz EDC Spray	Oz	EDC				EDP	EDP

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:320.32px;"><col style="width:71.29px;"><col style="width:71.29px;"><col style="width:96.95px;"><col style="width:23.76px;"><col style="width:23.76px;"><col style="width:23.76px;"><col style="width:150.18px;"><col style="width:89.35px;"></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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]EXAMPLE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]FIELD 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]FIELD 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]FIELD 3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]TERMS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]EQUIVALENCE[/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[/TD]

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/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"]5[/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"]6[/TD]

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

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

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

</tbody>

Cell Formula B2 =MID(SUBSTITUTE(" " & $A2," ",REPT(" ",99)),99*COLUMNS($B$1:B1),99)+0 C2 =TRIM(MID(SUBSTITUTE(" " & $A2," ",REPT(" ",99)),99*COLUMNS($B$1:C1),99)) D2 =IFERROR(LOOKUP(9.99E+307,SEARCH(H$2:H$7,A2),I$2:I$7),"") <tbody> </tbody>

<tbody>
</tbody>

This solution worked perfectly! Thank you!

 

[DanteAmor]

Thanks to everyone who replied to this thread.
This solution worked perfectly! Thank you!

Hi @helphelp, I'm glad to help you. Thanks for the feedback.