Extracting out specific text from within a cell

helphelp

New Member
Joined
Jun 29, 2019
Messages
8
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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))
 
Upvote 0
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))
 
Upvote 0
HI
Try this
Code:
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
 
Upvote 0
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>
 
Upvote 0
with your example you can try PowerQuery aka Get&Transform

RAWRAW.1RAW.2.1Custom
1 oz Eau De Toilette Spray1ozEDT
1.6 oz EDT Spray1.6ozEDT
3.4 ml Eau De Cologne Concentre Spray3.4mlEDC
3.4 oz Eau De Parfum Spray (Unisex)3.4ozEDP
2.8 oz EDP Spray (Limited Edition)2.8ozEDP

Code:
[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
 
Last edited:
Upvote 0
Thanks to everyone who replied to this thread.

Using a search table:

ABCDEFGHI
1EXAMPLEFIELD 1FIELD 2FIELD 3 TERMSEQUIVALENCE
21 oz Eau De Toilette Spray1ozEDT Eau De ToiletteEDT
31.6 oz EDT Spray1.6ozEDT EDTEDT
43.4 ml Eau De Cologne Concentre Spray3.4mlEDC Eau De CologneEDC
53.4 oz Eau De Parfum Spray (Unisex)3.4ozEDP EDCEDC
62.8 oz EDP Spray (Limited Edition)2.8ozEDP Eau De ParfumEDP
71 Oz EDC Spray1OzEDC EDPEDP

<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>
</tbody>

CellFormula
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!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top