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[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]

 

[jtakw]

Sorry, I'm still confused...
So, are you saying, if the 6 digit numbers After G# is separated by a "/", not a coma, then you Don't want it extracted?

May be, if you can show a few lines of data and what the results should be, it may be more clear.

 

[maayub]

no. I want all the data extracted after G. no matter how many sets they have in column A. I want the data exactly showing in message 5, but the formula you provided - it only extract the first set of values in column B (see table 8).

 

[maayub]

the formula is extracting multiple set of values but only if the data is separated with a /. if the data is separated with a comma it is only returning the 1st set of values.

 

[jtakw]

This is the table from Post #8 , I'm getting the correct results here...

Excel 2010
	A	B	C
14	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352, 202347	218867
15	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
16	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352, 202347	218867, 218357
17	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
18	G# 202352, 202347 ZRX MED 4984 LT/PIR WE 218867 DUE 4/30	202352, 202347	218867
19	Differ_GQQ02_02-10_IC_18-014_Due_12/25
20	G#222237/222240/222244 TUE 4911/4913/4940 PI/CFZ WE 220917 DUE 04/30	222237, 222240, 222244	220917
21	G# 202352,202347 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 4/30	202352, 202347	218867, 218357
22	2018 BS OCM WRT Carport -ZXR-94 VIFG54 *WATER* LO (JLK 2)
23	G# 202352 ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14	202352	218867, 218357
24	ZRX MED 4984 LT/PIR WE 218867, 218357 DUE 9/14		218867, 218357
25	G# 202352 ZRX MED 4984 LT/PIR	202352
Sheet23

Cell Formulas
Range	Formula
B14	=IF(LEFT(A14,2)="G#",TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A14,3,255)),", ",",")," ",REPT(" ",100)),",",", "),"/",", "),50)),"")
C14	=IF(ISNUMBER(SEARCH("WE",A14)),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A14,SEARCH("WE",A14)+3,255),", ",",")," ",REPT(" ",100)),",",", "),50)),"")

 

[jtakw]

Got your PM, that's because you have a SPACE Before And After the coma, you've never shown any sample where this happens, I'll adjust the formula for this, will post back.

 

[jtakw]

Here, this will handle SPACE COMA SPACE as well as COMA SPACE, and COMA only:

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
13	G# 201872 , 201871 GH2 SK-452, 453, 454 LTR/ZPR INSPECTION LFIP WE 218765 , 218762 , 218770	201872, 201871	218765, 218762, 218770
14	G# 205326 , 205166 FS1 MEDS 4910, 4911 LTR/ZPR INSPECTION EVERY SUNDAY	205326, 205166
Sheet23

Cell Formulas
Range	Formula
B1	=IF(LEFT(A1,2)="G#",TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,3,255))," , ",","),", ",",")," ",REPT(" ",100)),",",", "),"/",", "),50)),"")
C1	=IF(ISNUMBER(SEARCH("WE",A1)),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("WE",A1)+3,255)," , ",","),", ",",")," ",REPT(" ",100)),",",", "),50)),"")

 

[maayub]

Thank you for all the help.

 

[jtakw]

You're welcome. Welcome to the forum BTW.