Extract data from cell

[PresidentEvil]

Hi,

I have been working on a VBA macro in outlook that takes input from user and update the excel sheet with the user inputs along with the email subject line and body.

Emails are not in a specific format. Subject line and body (ofcourse) are dynamic.

I want something that can fetch the data from the subject line data that is saved in excel sheet.

Let's say, I have subject line as "Hello, this is a test xxxxxxxxxxWCxx"

I want to extract that last part highlighted (let's call it ID for now) into new row.

This ID can be anywhere in the subject, maybe in the middle or at the beginning or at the end (just like example above). There is also a possibility of having hyphen in that ID. Ex: xxxxx-xxxxx-WC-xx

Can someone please help me with a formula to extract this ID? I tried Right, left and mid formula, but it won't always work.

Any help is highly appreciated!

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B
2	I am a silly XxxxxxxxxxxWCxx	XxxxxxxxxxxWCxx
3	he is Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx
4	Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx
Sheet19

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(MID(A2,MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1),IFERROR(SEARCH(" ",A2,SEARCH("WC",A2)),LEN(A2))-MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1)+1))

Here it is, spread out a bit for greater understanding:

MrExcelPlayground12.xlsx
	A	B	C	D	E	F
2	I am a silly XxxxxxxxxxxWCxx	XxxxxxxxxxxWCxx	25	13	28	XxxxxxxxxxxWCxx
3	he is Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx	19	6	24	Xxxxx-xxxxx-WC-xx
4	Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx	13	1	18	Xxxxx-xxxxx-WC-xx
Sheet19

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(MID(A2,MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1),IFERROR(SEARCH(" ",A2,SEARCH("WC",A2)),LEN(A2))-MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1)+1))
C2:C4	C2	=SEARCH("WC",A2)
D2:D4	D2	=MAX(C2-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,C2))," ", REPT(" ", 99)), 99))),1)
E2:E4	E2	=IFERROR(SEARCH(" ",A2,C2),LEN(A2))
F2:F4	F2	=TRIM(MID(A2,D2,E2-D2+1))

 

[PresidentEvil]

MrExcelPlayground12.xlsx
	A	B
2	I am a silly XxxxxxxxxxxWCxx	XxxxxxxxxxxWCxx
3	he is Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx
4	Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx
Sheet19

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(MID(A2,MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1),IFERROR(SEARCH(" ",A2,SEARCH("WC",A2)),LEN(A2))-MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1)+1))

Here it is, spread out a bit for greater understanding:

MrExcelPlayground12.xlsx
	A	B	C	D	E	F
2	I am a silly XxxxxxxxxxxWCxx	XxxxxxxxxxxWCxx	25	13	28	XxxxxxxxxxxWCxx
3	he is Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx	19	6	24	Xxxxx-xxxxx-WC-xx
4	Xxxxx-xxxxx-WC-xx at that	Xxxxx-xxxxx-WC-xx	13	1	18	Xxxxx-xxxxx-WC-xx
Sheet19

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(MID(A2,MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1),IFERROR(SEARCH(" ",A2,SEARCH("WC",A2)),LEN(A2))-MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1)+1))
C2:C4	C2	=SEARCH("WC",A2)
D2:D4	D2	=MAX(C2-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,C2))," ", REPT(" ", 99)), 99))),1)
E2:E4	E2	=IFERROR(SEARCH(" ",A2,C2),LEN(A2))
F2:F4	F2	=TRIM(MID(A2,D2,E2-D2+1))

Thank you, that's what I was looking for.

One final requirement, can that formula be altered to have the final outcome to be in xxxxxxxxxxWCxx and not xxxxx-xxxxx-wc-xx?

Some of the subject line is without hypen,but the one with hypen should not be with hypen after formula

 

[JamesCanale]

SUBSTITUTE will work for that.

MrExcelPlayground12.xlsx
	A	B
2	I am a silly XxxxxxxxxxxWCxx	XxxxxxxxxxxWCxx
3	he is Xxxxx-xxxxx-WC-xx at that	XxxxxxxxxxWCxx
4	Xxxxx-xxxxx-WC-xx at that	XxxxxxxxxxWCxx
Sheet19

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(TRIM(MID(A2,MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1),IFERROR(SEARCH(" ",A2,SEARCH("WC",A2)),LEN(A2))-MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1)+1)),"-","")

 

[PresidentEvil]

SUBSTITUTE will work for that.

MrExcelPlayground12.xlsx
	A	B
2	I am a silly XxxxxxxxxxxWCxx	XxxxxxxxxxxWCxx
3	he is Xxxxx-xxxxx-WC-xx at that	XxxxxxxxxxWCxx
4	Xxxxx-xxxxx-WC-xx at that	XxxxxxxxxxWCxx
Sheet19

Cell Formulas
Range	Formula
B2:B4	B2	=SUBSTITUTE(TRIM(MID(A2,MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1),IFERROR(SEARCH(" ",A2,SEARCH("WC",A2)),LEN(A2))-MAX(SEARCH("WC",A2)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A2,SEARCH("WC",A2)))," ", REPT(" ", 99)), 99))),1)+1)),"-","")

Perfect!