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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Jborg

Board Regular
Joined
Feb 3, 2012
Messages
165
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

Board Regular
Joined
Feb 3, 2012
Messages
165
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,185
Office Version
  1. 2013
Platform
  1. Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,957
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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:

helphelp

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

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,907
Latest member
zerocool88

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
Top