Find and render text within IF Function

[David P.]

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!

 

[Luke M]

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" ]

 

[Luke M]

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" ]

 

[David P.]

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.

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!*

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.]

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.

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!*

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.]

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.

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.

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!*

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.]

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.

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.

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!*

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!