Extract data after special characters

[maayub]

I want to extract data from a cell containing this information:

G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30

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

<tbody>
</tbody>

 

[jtakw]

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
	A	B	C	D
1	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30		202352,202347	218867
2	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30		222237/222240/222244	220917
3	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30		202352,202347	218867,218357
4	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14		202352	218867,218357
Sheet23

Cell Formulas
Range	Formula
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.

 

[maayub]

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:

	A	B	C
1	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352, 202347	218867
2	Differ_GQQ02_02-10_IC_18-014_Due_12/25
3	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
4	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352, 202347	218867, 218357
5	2018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
6	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357

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

 

[jtakw]

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
	A	B	C
1	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352, 202347	218867
2	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
3	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352, 202347	218867, 218357
4	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
5	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352, 202347	218867
6	Differ_GQQ02_02-10_IC_18-014_Due_12/25
7	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
8	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352, 202347	218867, 218357
9	2018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
10	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
11	ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14		218867, 218357
12	G# 202352 ZRX MED 4984 LT/PIR	202352
Sheet23

Cell Formulas
Range	Formula
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)),"")

 

[jtakw]

Just a hair shorter for the first formula:

Excel 2010
	A	B	C
1	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352, 202347	218867
2	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
3	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352, 202347	218867, 218357
4	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
5	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352, 202347	218867
6	Differ_GQQ02_02-10_IC_18-014_Due_12/25
7	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
8	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352, 202347	218867, 218357
9	2018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
10	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
11	ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14		218867, 218357
12	G# 202352 ZRX MED 4984 LT/PIR	202352
Sheet23

Cell Formulas
Range	Formula
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)),"")

 

[maayub]

Column B1 is still returning the first set of numbers.

 

[jtakw]

Column B1 is still returning the first set of numbers.

What does that mean?

Looks right to me in my samples.

 

[maayub]

What does that mean?

Looks right to me in my samples.

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.

	A	B	C
1	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352	218867
2	G#2 22237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
3	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352	218867, 218357
4	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
5	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352	218867
6	Differ_GQQ02_02-10_IC_18-014_Due_12/25
7	G#2 22237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
8	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352	218867, 218357
9	2018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
10	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
11	ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14		218867, 218357
12	G# 202352 ZRX MED 4984 LT/PIR	202352

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

 

[jtakw]

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.

 

[maayub]

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.