Formula assistance

[James Carlo Cruz]

Hi Excel Experts :

I would like to ask if there is a formula to segregate this text sample into columns :
Caleb Pearson843-619-1504askcpteam@gmail.com

in to Name Column / Phone Column / email / website

 

[Hui]

Are you able to get a new export of the data source with either Commas or some specific character between each field ?
Caleb Pearson,843-619-1504,askcpteam@gmail.com,http://myaddress.com

 

[John Jairo V]

Hi, @James Carlo Cruz!

Try this:
[B1] : =LEFT(A1,MATCH(1,INDEX(--ISNUMBER(--MID(A1,ROW($1:$99),1)),),)-1)
[C1] : =MID(A1,SEARCH("???-???-????",A1),12)
[D1] : =SUBSTITUTE(MID(A1,SEARCH("???-???-????",A1)+13,99),E1,"")
[E1] : =IFERROR(MID(A1,SEARCH("http:",A1),99),"")

And drag it down. Blessings!

 

[James Carlo Cruz]

thanks John

 

[James Carlo Cruz]

@Hui that is the format I usually get it

 

[James Carlo Cruz]

@Hui and @john how about when I don't have phone number : like this
Virginia A Rice
gini.rice@crescenthomes.net
http://www.crescenthomes.net
it shows error

 

[Hui]

I would use an =iferror(existing function,"No Phone Number")
around the Phone number function

 

[James Carlo Cruz]

Hi Hui,

Yes I've done that but since email formula is dependent in the phone number formula, the email column will show value error

 

[Hui]

So set it up to return 0 or "" as the answer so that it has zero value and zero length
eg: =iferror(existing function,0)
or
=iferror(existing function,"")

 

[John Jairo V]

I just realized that you have as a separator of words the character (10) between your texts. That being the case, you can use the unique following formula:

[B1] =TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",999)),1+999*(COLUMNS($B1:B1)-1),999))

And drag it to right and down.

If you have more problems, upload an excel file with more representative examples and how you would like it to stay. Blessings!

Note: You can use text to columns too for separate text. I like formula approach, because is dinamic, but text to columns every time you have to do it.

 

[James Carlo Cruz]

Hi John / Hui,

Thanks for the responses. Your formulas are really helpful I was able to submit it on time.