Formula to remove just numeric values.

[phillipc1]

Hello all,

I am working on a way to extract just the numeric values from an email address as this numeric value is a unique employee ID. I have an example below of what I would like to do, I just can't figure out the best formula to achieve this. All of the values will be presented in the same exact format with the exception being the exact amount of numeric values listed.

Data examples provided:
d12345@genericemail.com
d1234@genericemail.com
d123456@genericemail.com
d12@genericemail.com

Outputs needed:
12345
1234
123456
12

I tried using a mid function, but was not sure if this would work or if I input it correctly as it was still providing an error.

=MID('G16,SEARCH("d",G16)+1,SEARCH("d",G16,SEARCH("d",G16)+1)-SEARCH("@",G16)-1)

I appreciate any assistance that can be provided in figuring this out.

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	d12345@genericemail.com	12345
3	d1234@genericemail.com	1234
4	d123456@genericemail.com	123456
5	d12@genericemail.com	12
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=CONCAT(TEXTSPLIT(UPPER(A2),CHAR(SEQUENCE(27,,64)),".",1))

 

[phillipc1]

How about

Fluff.xlsm
	A	B
1
2	d12345@genericemail.com	12345
3	d1234@genericemail.com	1234
4	d123456@genericemail.com	123456
5	d12@genericemail.com	12
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=CONCAT(TEXTSPLIT(UPPER(A2),CHAR(SEQUENCE(27,,64)),".",1))

It looks like this worked out! Just as another thought, it is possible for blanks to occur at which point I would want to return blank as opposed to "#VALUE". For that case, should I add an if(isblank( like below?

=IF(ISBLANK(A2),"",CONCAT(TEXTSPLIT(UPPER(A2),CHAR(SEQUENCE(27,,64)),".",1)))

 

[dragontbone]

Hello all,

I am working on a way to extract just the numeric values from an email address as this numeric value is a unique employee ID. I have an example below of what I would like to do, I just can't figure out the best formula to achieve this. All of the values will be presented in the same exact format with the exception being the exact amount of numeric values listed.

Data examples provided:
d12345@genericemail.com
d1234@genericemail.com
d123456@genericemail.com
d12@genericemail.com

Outputs needed:
12345
1234
123456
12

I tried using a mid function, but was not sure if this would work or if I input it correctly as it was still providing an error.

=MID('G16,SEARCH("d",G16)+1,SEARCH("d",G16,SEARCH("d",G16)+1)-SEARCH("@",G16)-1)

I appreciate any assistance that can be provided in figuring this out.

Try doing this:

=SUBSTITUTE(SUBSTITUTE(MID(G16,MIN(IF(ISNUMBER(--MID(G16,ROW(INDIRECT("1:" & LEN(G16))),1)),ROW(INDIRECT("1:" & LEN(G16))),""),IF(ISNUMBER(--MID(G16,ROW(INDIRECT("1:" & LEN(G16))),1)),ROW(INDIRECT("1:" & LEN(G16))),"")), "@genericemail.com", "")

 

[Fluff]

Often best to avoid ISBLANK and do it like

=IF(A2="","",CONCAT(TEXTSPLIT(UPPER(A2),CHAR(SEQUENCE(27,,64)),".",1)))

 

[Scott Huish]

How about:
=MID(TEXTBEFORE(A2,"@",,,,""),2,255)

 

[phillipc1]

Thank you all! I was able to get it working properly. I appreciate everyone's assistance on this.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[dragontbone]

Often best to avoid ISBLANK and do it like

=IF(A2="","",CONCAT(TEXTSPLIT(UPPER(A2),CHAR(SEQUENCE(27,,64)),".",1)))

Out of curiosity, why is it best to avoid ISBLANK?

 

[Fluff]

Because a cell that contains a formula returning "" is not a blank cell & catches a lot of people out.