Extract data one after a character after a special character

D

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 "-"

[TABLE="width: 217"]
<colgroup><col></colgroup><tbody>[TR]
[TD]DS2118-O/N-AXXS[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-BXS[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-CS[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-DM[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-EL[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-FXL[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-G2XL[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-G3XL[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-G4XL[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-G5XL[/TD]
[/TR]
[TR]
[TD]DS2118-O/N-G6XL

Thanks in advance[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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
jtakw said:
Hi,

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

ABC
DS2118-O/N-AXXSXXS
DS2118-O/N-BXSXS
DS2118-O/N-CSS
DS2118-O/N-DMM
DS2118-O/N-ELL
DS2118-O/N-FXLXL
DS2118-O/N-G2XL2XL
DS2118-O/N-G3XL3XL
DS2118-O/N-G4XL4XL
DS2118-O/N-G5XL5XL
DS2118-O/N-G6XL6XL

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]

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

[TD="align: center"]3[/TD]

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

[TD="align: center"]4[/TD]

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

[TD="align: center"]5[/TD]

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

[TD="align: center"]6[/TD]

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

[TD="align: center"]7[/TD]

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

[TD="align: center"]8[/TD]

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

[TD="align: center"]9[/TD]

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

[TD="align: center"]10[/TD]

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

[TD="align: center"]11[/TD]

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

</tbody>
Sheet517​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=MID(A1,SEARCH("/N-",A1)+4,99)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

If your data format varies, please give more samples.
Click to expand...

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

[TABLE="width: 217"]
<colgroup><col></colgroup><tbody>[TR]
[TD]DT1139-NAV-R077[/TD]
[/TR]
[TR]
[TD]DT1139-NAV-R082[/TD]
[/TR]
[TR]
[TD]DT1139-NAV-R087[/TD]
[/TR]
[TR]
[TD]DT1139-NAV-R092[/TD]
[/TR]
[TR]
[TD]DT1139-NAV-R097[/TD]
[/TR]
[TR]
[TD]DT1139-NAV-R102[/TD]
[/TR]
[TR]
[TD]DT1139-NAV-R107[/TD]
[/TR]
[TR]
[TD]DT1139-NAV-R112[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R077[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R082[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R087[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R092[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R097[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R102[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R107[/TD]
[/TR]
[TR]
[TD]DT1147-NAV-R112[/TD]
[/TR]
[TR]
[TD]DT1140-NAV-L074[/TD]
[/TR]
[TR]
[TD]DT1140-NAV-L079[/TD]
[/TR]
[TR]
[TD]DT1140-NAV-L084[/TD]
[/TR]
[TR]
[TD]DT1140-NAV-L089[/TD]
[/TR]
[TR]
[TD]DT1140-NAV-L094[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 217"]
<colgroup><col></colgroup><tbody>[TR]
[TD]DS1118-ORA-EL[/TD]
[/TR]
[TR]
[TD]DS1118-ORA-FXL[/TD]
[/TR]
[TR]
[TD]DS1118-ORA-G2XL[/TD]
[/TR]
[TR]
[TD]DS1118-ORA-G3XL[/TD]
[/TR]
[TR]
[TD]DS1118-ORA-G4XL[/TD]
[/TR]
[TR]
[TD]DS1118-ORA-G5XL[/TD]
[/TR]
[TR]
[TD]DS1118-ORA-G6XL[/TD]
[/TR]
[TR]
[TD]DS1118T1-ORA-AXXS[/TD]
[/TR]
</tbody>[/TABLE]
 
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
You must log in or register to reply here.

Similar threads

D
  • Question Question
Remove a character in a cell
Replies
6
Views
390
Siddharth Rout
Siddharth Rout
D
  • Question Question
Remove 1st char after a special char
Replies
3
Views
287
Alex Blakenburg
A
E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
378
Fluff
Fluff
D
  • Question Question
Extract and replace particular character in a cell
Replies
6
Views
456
jtakw
jtakw
D
  • Question Question
Extract a particular characters from a cell
2
Replies
14
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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