Find and render text within IF Function
 

This one is a little weird but this is what I'm trying to accomplish:

1) Find " CE" in both cells A1 & B1 (note there will only be one instance of
" CE" in both cells combined and the space before "CE" is intentional)

2) IF function #1: Once " CE" is found then render all text from the 1st "/
" (space after the / is intentional) to the left of " CE". In other words: /
find this text CE

3) IF function #2: If no "/ " is found to the left of " CE" then render all
text to the left of " CE" to the beginning of the cell.

Many thanks!

--
David P.

Find and render text within IF Function
 

Bulky, but I think this does what you requested.

=IF(ISNUMBER(FIND(" CE",A1)),IF(ISNUMBER(FIND("/ ",LEFT(A1,FIND("
CE",A1)-1))),MID(A1,FIND("/ ",A1),FIND(" CE",A1)-FIND("/ ",A1))&"
CE",LEFT(A1,FIND(" CE",A1)+2)),IF(ISNUMBER(FIND("/ ",LEFT(B1,FIND("
CE",B1)-1))),MID(B1,FIND("/ ",B1),FIND(" CE",B1)-FIND("/ ",B1))&"
CE",LEFT(B1,FIND(" CE",B1)+2)))

I wasn't sure whether you meant to include the " CE" in the returned value
or not. Formula is currently written to include it. If you wish to exclude
it, remove the two instances in formula where it calls out [ &" CE" ]
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David P." wrote:

This one is a little weird but this is what I'm trying to accomplish:

1) Find " CE" in both cells A1 & B1 (note there will only be one instance of
" CE" in both cells combined and the space before "CE" is intentional)

2) IF function #1: Once " CE" is found then render all text from the 1st "/
" (space after the / is intentional) to the left of " CE". In other words: /
find this text CE

3) IF function #2: If no "/ " is found to the left of " CE" then render all
text to the left of " CE" to the beginning of the cell.

Many thanks!

--
David P.

Find and render text within IF Function
 

Thank you Luke. Let me explain myself better in the format below. The
formulas you kindly wrote for me somehow weren't isolating a phone number
which is my objective. We are trying to isolate cell phone numbers. I thought
the best way to do that was to find " CE" since it always follows a cell
phone number as a standard. We likewise always use "/ " everytime we are
seperating two numbers. There are cases where there will be no "/ " since
there is only one number. I should have spelled it out like this orginally
that might allow you to tweak the forumlas to render the cell number. Here's
the idea:

340-8688 / 818-444-6122 CE (This would be A1)
775-8844 (This would be B1)

Or in some cases I may have it like this with no "/ "

818-444-6122 CE
775-8844

I will not need the " CE".
Many thanks again.
--
David P.


"Luke M" wrote:

Bulky, but I think this does what you requested.

=IF(ISNUMBER(FIND(" CE",A1)),IF(ISNUMBER(FIND("/ ",LEFT(A1,FIND("
CE",A1)-1))),MID(A1,FIND("/ ",A1),FIND(" CE",A1)-FIND("/ ",A1))&"
CE",LEFT(A1,FIND(" CE",A1)+2)),IF(ISNUMBER(FIND("/ ",LEFT(B1,FIND("
CE",B1)-1))),MID(B1,FIND("/ ",B1),FIND(" CE",B1)-FIND("/ ",B1))&"
CE",LEFT(B1,FIND(" CE",B1)+2)))

I wasn't sure whether you meant to include the " CE" in the returned value
or not. Formula is currently written to include it. If you wish to exclude
it, remove the two instances in formula where it calls out [ &" CE" ]
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David P." wrote:

This one is a little weird but this is what I'm trying to accomplish:

1) Find " CE" in both cells A1 & B1 (note there will only be one instance of
" CE" in both cells combined and the space before "CE" is intentional)

2) IF function #1: Once " CE" is found then render all text from the 1st "/
" (space after the / is intentional) to the left of " CE". In other words: /
find this text CE

3) IF function #2: If no "/ " is found to the left of " CE" then render all
text to the left of " CE" to the beginning of the cell.

Many thanks!

--
David P.

Find and render text within IF Function
 

Also, there may not always be 12 digits on the phone numbers (including the
dashes). Sometimes the cell number will be typed with 8 digits (including the
dash). Thanks.
--
David P.


"David P." wrote:

Thank you Luke. Let me explain myself better in the format below. The
formulas you kindly wrote for me somehow weren't isolating a phone number
which is my objective. We are trying to isolate cell phone numbers. I thought
the best way to do that was to find " CE" since it always follows a cell
phone number as a standard. We likewise always use "/ " everytime we are
seperating two numbers. There are cases where there will be no "/ " since
there is only one number. I should have spelled it out like this orginally
that might allow you to tweak the forumlas to render the cell number. Here's
the idea:

340-8688 / 818-444-6122 CE (This would be A1)
775-8844 (This would be B1)

Or in some cases I may have it like this with no "/ "

818-444-6122 CE
775-8844

I will not need the " CE".
Many thanks again.
--
David P.


"Luke M" wrote:

Bulky, but I think this does what you requested.

=IF(ISNUMBER(FIND(" CE",A1)),IF(ISNUMBER(FIND("/ ",LEFT(A1,FIND("
CE",A1)-1))),MID(A1,FIND("/ ",A1),FIND(" CE",A1)-FIND("/ ",A1))&"
CE",LEFT(A1,FIND(" CE",A1)+2)),IF(ISNUMBER(FIND("/ ",LEFT(B1,FIND("
CE",B1)-1))),MID(B1,FIND("/ ",B1),FIND(" CE",B1)-FIND("/ ",B1))&"
CE",LEFT(B1,FIND(" CE",B1)+2)))

I wasn't sure whether you meant to include the " CE" in the returned value
or not. Formula is currently written to include it. If you wish to exclude
it, remove the two instances in formula where it calls out [ &" CE" ]
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David P." wrote:

This one is a little weird but this is what I'm trying to accomplish:

1) Find " CE" in both cells A1 & B1 (note there will only be one instance of
" CE" in both cells combined and the space before "CE" is intentional)

2) IF function #1: Once " CE" is found then render all text from the 1st "/
" (space after the / is intentional) to the left of " CE". In other words: /
find this text CE

3) IF function #2: If no "/ " is found to the left of " CE" then render all
text to the left of " CE" to the beginning of the cell.

Many thanks!

--
David P.