Extract data one after a character after a special character

DQ2013

New Member
Joined
Nov 7, 2013
Messages
31
Hi I ma hope some one can help me with the following . I have the follow data . I need to return the size only i.e XXS, XS S M,L,XL,2XL...which starts from one character after the "-"

DS2118-O/N-AXXS
DS2118-O/N-BXS
DS2118-O/N-CS
DS2118-O/N-DM
DS2118-O/N-EL
DS2118-O/N-FXL
DS2118-O/N-G2XL
DS2118-O/N-G3XL
DS2118-O/N-G4XL
DS2118-O/N-G5XL
DS2118-O/N-G6XL

Thanks in advance

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,

If your data is Always in the format as shown in your sample:


Book1
ABC
1DS2118-O/N-AXXSXXS
2DS2118-O/N-BXSXS
3DS2118-O/N-CSS
4DS2118-O/N-DMM
5DS2118-O/N-ELL
6DS2118-O/N-FXLXL
7DS2118-O/N-G2XL2XL
8DS2118-O/N-G3XL3XL
9DS2118-O/N-G4XL4XL
10DS2118-O/N-G5XL5XL
11DS2118-O/N-G6XL6XL
Sheet517
Cell Formulas
RangeFormula
C1=MID(A1,SEARCH("/N-",A1)+4,99)


Formula copied down.

If your data format varies, please give more samples.
 
Upvote 0
Hi,

If your data is Always in the format as shown in your sample:

ABC
1DS2118-O/N-AXXSXXS
2DS2118-O/N-BXSXS
3DS2118-O/N-CSS
4DS2118-O/N-DMM
5DS2118-O/N-ELL
6DS2118-O/N-FXLXL
7DS2118-O/N-G2XL2XL
8DS2118-O/N-G3XL3XL
9DS2118-O/N-G4XL4XL
10DS2118-O/N-G5XL5XL
11DS2118-O/N-G6XL6XL

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet517

Worksheet Formulas
CellFormula
C1=MID(A1,SEARCH("/N-",A1)+4,99)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Formula copied down.

If your data format varies, please give more samples.

Thanks for this sorry to mention that my data format varies further examples are as follows:

DT1139-NAV-R077
DT1139-NAV-R082
DT1139-NAV-R087
DT1139-NAV-R092
DT1139-NAV-R097
DT1139-NAV-R102
DT1139-NAV-R107
DT1139-NAV-R112
DT1147-NAV-R077
DT1147-NAV-R082
DT1147-NAV-R087
DT1147-NAV-R092
DT1147-NAV-R097
DT1147-NAV-R102
DT1147-NAV-R107
DT1147-NAV-R112
DT1140-NAV-L074
DT1140-NAV-L079
DT1140-NAV-L084
DT1140-NAV-L089
DT1140-NAV-L094

<colgroup><col></colgroup><tbody>
</tbody>

DS1118-ORA-EL
DS1118-ORA-FXL
DS1118-ORA-G2XL
DS1118-ORA-G3XL
DS1118-ORA-G4XL
DS1118-ORA-G5XL
DS1118-ORA-G6XL
DS1118T1-ORA-AXXS

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
Hmmm...I don't see any "size" information in the first set above? Are they the numbers after "R", "L", etc..?
 
Last edited:
Upvote 0
I'll be signing off now, so assuming the Numbers are the Sizes for the top set in your new sample, and assuming you Always have 2 hyphens in the Text string:


Book1
ABC
1DS2118-O/N-AXXSXXS
2DS2118-O/N-BXSXS
3DS2118-O/N-CSS
4DS2118-O/N-DMM
5DS2118-O/N-ELL
6DS2118-O/N-FXLXL
7DS2118-O/N-G2XL2XL
8DS2118-O/N-G3XL3XL
9DS2118-O/N-G4XL4XL
10DS2118-O/N-G5XL5XL
11DS2118-O/N-G6XL6XL
12DT1139-NAV-R077077
13DT1139-NAV-R082082
14DT1139-NAV-R087087
15DT1139-NAV-R092092
16DT1139-NAV-R097097
17DT1139-NAV-R102102
18DT1139-NAV-R107107
19DT1139-NAV-R112112
20DT1140-NAV-L074074
21DT1140-NAV-L079079
22DT1140-NAV-L084084
23DT1140-NAV-L089089
24DT1140-NAV-L094094
25DS1118-ORA-ELL
26DS1118-ORA-FXLXL
27DS1118-ORA-G2XL2XL
28DS1118-ORA-G3XL3XL
29DS1118-ORA-G4XL4XL
30DS1118-ORA-G5XL5XL
31DS1118-ORA-G6XL6XL
32DS1118T1-ORA-AXXSXXS
Sheet517
Cell Formulas
RangeFormula
C1=MID(A1,FIND("-",A1,FIND("-",A1)+1)+2,99)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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