Using an "IF" statement to find a specific character in a string

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows
I have a column with over 15,000 values. These values are either website addresses or email addresses. I like to separate them into two columns
  1. Email Address
  2. Web Address
I was thinking of using an IF statement to find the "@" character to distinguish if the cell has an email address or website address. How can I find a specific character in a string and then use the IF statement? or May be there is a better way of arranging this data in two separate columns using a different formula.

This is what I am trying to do:
Column B is the original data set
Column C & D are the new columns separating the addresses in proper columns.
1617916239596.png


Thank you in advance for the help.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
If you don't want blank rows, another option would be
+Fluff 1.xlsm
ABCD
1
2emailwebsite
3htttp://abc.comabc@gmail.comhtttp://abc.com
4htttp://abc.comab.cd@gmail.comhtttp://abc.com
5abc.comabc.com
6abc@gmail.com
7ab.cd@gmail.com
8
Lists
Cell Formulas
RangeFormula
C3:C4C3=FILTER(B3:B100,ISNUMBER(FIND("@",B3:B100)))
D3:D5D3=FILTER(B3:B100,(B3:B100<>"")*(ISERR(FIND("@",B3:B100))))
Dynamic array formulas.
 

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Hi,

Something like this?

Book3.xlsx
ABCD
1
2emailwebsite
3htttp://abc.com htttp://abc.com
4htttp://abc.com htttp://abc.com
5abc.com abc.com
6abc@gmail.comabc@gmail.com 
7ab.cd@gmail.comab.cd@gmail.com 
Sheet902
Cell Formulas
RangeFormula
C3:C7C3=IF(ISNUMBER(FIND("@",B3)),B3,"")
D3:D7D3=IF(C3="",B3,"")


Note: htttp is not a typo, I just didn't want my Excel to light it up as a link.
This works. Thank you. However, I just realized that I have other items in the source column that I don't care. How do I omit that? e.g.,
What do I need to do to keep the cell empty if it is not an email or a web address? Currently, the way the formula is written, it would give me the anything in Column D except the email address.

Any thoughts?
Thanks

1617919633614.png
1617919682142.png
 

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you don't want blank rows, another option would be
+Fluff 1.xlsm
ABCD
1
2emailwebsite
3htttp://abc.comabc@gmail.comhtttp://abc.com
4htttp://abc.comab.cd@gmail.comhtttp://abc.com
5abc.comabc.com
6abc@gmail.com
7ab.cd@gmail.com
8
Lists
Cell Formulas
RangeFormula
C3:C4C3=FILTER(B3:B100,ISNUMBER(FIND("@",B3:B100)))
D3:D5D3=FILTER(B3:B100,(B3:B100<>"")*(ISERR(FIND("@",B3:B100))))
Dynamic array formulas.

This works but it does not keep the data in the same row as the original. However, I like it. May be this formula will come in hand in some other way. Thank you for sharing
 

Seba Robles

Board Regular
Joined
May 16, 2018
Messages
61
Another alternative is the following,

On column A have your data, on column B you can name it "Data Type" or something and then use this formula on the range that applies (in my case it's B2:B9)

1617920332563.png

Excel Formula:
=IFERROR(IF(FIND("@",A2,1),"Email"),"Website")

Then you can use filters to view whichever data type you need or you could even create a pivot table and organize it as you please

1617920494152.png
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

This works. Thank you. However, I just realized that I have other items in the source column that I don't care. How do I omit that? e.g.,
What do I need to do to keep the cell empty if it is not an email or a web address? Currently, the way the formula is written, it would give me the anything in Column D except the email address.

Any thoughts?
Thanks

Try this updated version, Caveat, lines like B6 will be problematic:

Book3.xlsx
BCD
2emailwebsite
3http://abc.com http://abc.com
4http://abc.com http://abc.com
5123-567-8900 
6abc.com abc.com
7some text 
8abc@gmail.comabc@gmail.com 
9ab.cd@gmail.comab.cd@gmail.com 
Sheet902
Cell Formulas
RangeFormula
C8:C9,C6,C3:C4C3=IF(ISNUMBER(FIND("@",B3)),B3,"")
D3:D9D3=IF(AND(C3="",OR(ISNUMBER(SEARCH("?.?",B3)),ISNUMBER(FIND("http",B3)))),B3,"")
 
Solution

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Forum statistics

Threads
1,143,907
Messages
5,721,440
Members
422,362
Latest member
elliotpat

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
Top