Extract data after special characters

M

maayub

New Member
Joined
Apr 15, 2018
Messages
8
I want to extract data from a cell containing this information:

G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30[TABLE="width: 663"]
<tbody>[TR]
[TD]
G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30
G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30[TABLE="width: 663"]
<tbody>[TR]
[TD]G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14

1. I want to extract data after G#. This data is always 6 digits. This 6 digit data is sometimes in 1 set or up to 5 sets separated by a comma or /. See examples above.

2. I also want to extract the data after "WE". this data is also 6 digit long. Sometimes in 1 set or up to 5 sets.


I want both sets of data display in two different columns. How can I do that?
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi,

This will get it started, you didn't say how you want the results formatted, as the source data is not "uniform":


Excel 2010
ABCD
1G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30202352,202347218867
2G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30222237/222240/222244220917
3G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30202352,202347218867,218357
4G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14202352218867,218357
Sheet23
Cell Formulas
RangeFormula
C1=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,3,255)),", ",",")," ",REPT(" ",100)),50))
D1=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("WE",A1)+3,255),", ",",")," ",REPT(" ",100)),50))


Formulas copied down.
 
Upvote 0
My apologies. I was not specific regarding this formula. I want to extract the 6 digit data after "G#" on each cell (if it exists) in cell B separating with a "comma space" and also want to extract the data after "WE " in cell C (if it exist) separating with a comma space. Please see the examples below:

[TABLE="width: 782"]
<colgroup><col width="64" style="width: 48pt;"> <col width="581" style="width: 436pt; mso-width-source: userset; mso-width-alt: 21248;"> <col width="210" style="width: 158pt; mso-width-source: userset; mso-width-alt: 7680;"> <col width="186" style="width: 140pt; mso-width-source: userset; mso-width-alt: 6802;"> <tbody>[TR]
[TD="class: xl1859, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl1856, width: 581, bgcolor: transparent"]A[/TD]
[TD="class: xl1856, width: 210, bgcolor: transparent"]B[/TD]
[TD="class: xl1858, width: 186, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="class: xl1853, bgcolor: transparent"]1[/TD]
[TD="class: xl1861, bgcolor: transparent"]G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30[/TD]
[TD="class: xl1862, bgcolor: transparent"]202352, 202347[/TD]
[TD="class: xl1857, bgcolor: transparent"]218867[/TD]
[/TR]
[TR]
[TD="class: xl1853, bgcolor: transparent"]2[/TD]
[TD="class: xl1861, bgcolor: transparent"]Differ_GQQ02_02-10_IC_18-014_Due_12/25[/TD]
[TD="class: xl1862, bgcolor: transparent"] [/TD]
[TD="class: xl1857, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl1853, bgcolor: transparent"]3[/TD]
[TD="class: xl1861, bgcolor: transparent"]G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30[/TD]
[TD="class: xl1861, bgcolor: transparent"]222237, 222240, 222244[/TD]
[TD="class: xl1857, bgcolor: transparent"]220917[/TD]
[/TR]
[TR]
[TD="class: xl1853, bgcolor: transparent"]4[/TD]
[TD="class: xl1861, bgcolor: transparent"]G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30[/TD]
[TD="class: xl1862, bgcolor: transparent"]202352, 202347[/TD]
[TD="class: xl1852, bgcolor: transparent"]218867, 218357[/TD]
[/TR]
[TR]
[TD="class: xl1853, bgcolor: transparent"]5[/TD]
[TD="class: xl1861, bgcolor: transparent"]2018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)[/TD]
[TD="class: xl1862, bgcolor: transparent"] [/TD]
[TD="class: xl1852, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl1854, bgcolor: transparent"]6[/TD]
[TD="class: xl1855, bgcolor: transparent"]G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14[/TD]
[TD="class: xl1855, bgcolor: transparent"]202352[/TD]
[TD="class: xl1860, bgcolor: transparent"]218867, 218357[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,

For some reason, I wasn't notified you had replied above, just checked my own postings and saw your reply, see if this covers your different scenarios:


Excel 2010
ABC
1G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30202352, 202347218867
2G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30222237, 222240, 222244220917
3G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30202352, 202347218867, 218357
4G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14202352218867, 218357
5G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30202352, 202347218867
6Differ_GQQ02_02-10_IC_18-014_Due_12/25
7G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30222237, 222240, 222244220917
8G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30202352, 202347218867, 218357
92018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
10G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14202352218867, 218357
11ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14218867, 218357
12G# 202352 ZRX MED 4984 LT/PIR202352
Sheet23
Cell Formulas
RangeFormula
B1=IF(ISNUMBER(SEARCH("G#",A1)),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,3,255)),", ",",")," ",REPT(" ",100)),",",", "),"/",", "),50)),"")
C1=IF(ISNUMBER(SEARCH("WE",A1)),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("WE",A1)+3,255),", ",",")," ",REPT(" ",100)),",",", "),50)),"")
 
Upvote 0
Just a hair shorter for the first formula:


Excel 2010
ABC
1G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30202352, 202347218867
2G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30222237, 222240, 222244220917
3G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30202352, 202347218867, 218357
4G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14202352218867, 218357
5G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30202352, 202347218867
6Differ_GQQ02_02-10_IC_18-014_Due_12/25
7G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30222237, 222240, 222244220917
8G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30202352, 202347218867, 218357
92018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
10G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14202352218867, 218357
11ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14218867, 218357
12G# 202352 ZRX MED 4984 LT/PIR202352
Sheet23
Cell Formulas
RangeFormula
B1=IF(LEFT(A1,2)="G#",TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,3,255)),", ",",")," ",REPT(" ",100)),",",", "),"/",", "),50)),"")
C1=IF(ISNUMBER(SEARCH("WE",A1)),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("WE",A1)+3,255),", ",",")," ",REPT(" ",100)),",",", "),50)),"")
 
Upvote 0
jtakw said:
What does that mean?

Looks right to me in my samples.
Click to expand...

it only returns extra set of G#'s with a "/" (examples above #2 and #7 ). Still returning single set of values with multiple G# separated with a comma. See below. Thanks.

ABC
G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30202352
G#2 22237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30222237, 222240, 222244
G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30202352218867, 218357
G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14218867, 218357
G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30202352
Differ_GQQ02_02-10_IC_18-014_Due_12/25
G#2 22237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30222237, 222240, 222244
G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30202352218867, 218357
2018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14218867, 218357
ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14218867, 218357
G# 202352 ZRX MED 4984 LT/PIR

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>
 
Upvote 0
I'm not clear on what you're saying.
Are you saying that some of your data Starts with G#2 and some with just G#?
If it starts with G#2, you want it ignored, or still extract the number strings?
What other possibilities are there, cause G#2 was never in your previous samples.
 
Upvote 0
No - when I posted my last message it just highlighted "G#2" for some reason. All I want is to extract the data starts with G# in column B and extract data after WE is column C (if G#'s and WE numbers are present in column A). The formula you provided in message #5 for column B is not giving me multiple sets of G values - it only extract the first set of values except when the 6 digit data is separated by a "/". When I enter the formula in column B it shows the data as shown in table 8. Hope this will clarify. the formula for column C is working fine. thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Extract data from one sheet to another using formulas
Replies
0
Views
957
c_schleimer
C
X
  • Question Question
extracting expesific data from prodoct list
Replies
3
Views
1K
Akashwani
Akashwani
B
  • Question Question
Extract data from multiple tables to a single list
Replies
1
Views
1K
Berries
B

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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