How to extract partial string onto another cell if conditions are met?

[fleabot]

Hi

I was wondering if someone could help me with a formula I am having a hard time coming up with:

I have an excel sheet with various recipient addresses. The recipient name column, say column A, will look something similar to "Ship to business #1584" or "Ship to business #1224" or "Jane Doe". I need to extract the 4 digits after # onto another column, say column C. I think I have the formula to that which is =REPLACE(A2,1,FIND("#",A2),"") . However, for all the recipient names that DO NOT have the text "ship to business", I need it to come back as 8119 in column C. How would I create that formula to extract the 4 digits but also include the 8119 if there is no business # on the recipient name? I included sample table below:

recipient name (column A)	recipient address (column B)	identifier (Column C)
Jane Doe	123 Hello Panda Lane	8119
C/O Business #1234	456 Kitty Paw Street	1234
C/O Business #2456	789 Chihiro Court	2456
Michael Smith	7655 Forest Lane	8119

<tbody>
</tbody>

I appreciate any help!
Thank you

 

[PaddyD]

hi - welcome to the board

=IF(ISNUMBER(FIND("#",A1)),--REPLACE(A1,1,FIND("#",A1),""),8119)

I added the -- in front of the replace() to coerce what would otherwise be a text string to a number - this may or may not be important.

 

[fleabot]

hi - welcome to the board

=IF(ISNUMBER(FIND("#",A1)),--REPLACE(A1,1,FIND("#",A1),""),8119)

I added the -- in front of the replace() to coerce what would otherwise be a text string to a number - this may or may not be important.

Thank you for replying me! I tried that formula and it works great. Although I have one worry, is there a way to make sure only recipient names that contains the text "C/O Business" in the cell show up with the 4 digit #? For example, if the recipient name was Jane Doe #123 , the result would still need to come back as 8119 because it does not contain "C/O Business" in its name. I hope that makes sense.

 

[PaddyD]

"...is there a way to make sure only recipient names that contains the text "C/O Business" in the cell show up with the 4 digit #?"

Will depend on how variable the data entry rules are. Given your example data, this would do it:

=IF(ISNUMBER(FIND("C/O Business #",A1)),--REPLACE(A1,1,FIND("#",A1),""),8119)

 

[Rick Rothstein]

Here is another formula for you to consider...

=IFERROR(0+MID(A1,SEARCH("C/O Business #",A1)+14,4),8119)