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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]recipient name (column A)[/TD]
[TD]recipient address (column B)[/TD]
[TD]identifier (Column C)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]123 Hello Panda Lane[/TD]
[TD]8119[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C/O Business #1234[/TD]
[TD]456 Kitty Paw Street[/TD]
[TD]1234[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C/O Business #2456[/TD]
[TD]789 Chihiro Court[/TD]
[TD]2456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michael Smith[/TD]
[TD]7655 Forest Lane[/TD]
[TD]8119[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)