text and number separation

[SubliminalJones]

Hello everyone...


I'm trying to do a spreadsheet that logs contact names and number pulled from a network application. Normally I copy and paste the information into one cell.

(cell a1) Ex: Steven Tyler p: 123-456-7890 f:369-125-1821.

How can I get 3 cells to seperate these and return:

Steven Tyler (in b1)
123-456-7890 (in c1)
369-125-1821 (in d1)

i cannot get it to remove the "p:" or "f:".


Please help if possible>

Thanks, SJ

[Dave O]

Formula in B1:
=MID(A1,1,FIND("p: ",A1,1)-2)

Formula in C1:
=MID(A1,FIND("p: ",A1,1)+3,12)

Formula in D1:
=RIGHT(A1,12)

These formulas rely on the assumption that the cell in column A will
always have "p: " and that telephone numbers will always be in
###-###-#### format.

[MatthewTap]

SJ --

And, if the telephone numbers have the possibility of coming in different
lengths, then use what Dave O said and something kind of clumsy like this,
since you mentioned the "f:" and "p:"


=RIGHT(a1,(((LEN(a1)-1)) - (SEARCH("f:",a1))))

"Dave O" wrote:

> Formula in B1:
> =MID(A1,1,FIND("p: ",A1,1)-2)
>
> Formula in C1:
> =MID(A1,FIND("p: ",A1,1)+3,12)
>
> Formula in D1:
> =RIGHT(A1,12)
>
> These formulas rely on the assumption that the cell in column A will
> always have "p: " and that telephone numbers will always be in
> ###-###-#### format.
>
>