IF Statement Based on Text Search

[housinganalyst]

Hi all,

I've just joined the forum in hope that someone might be able to help me with an issue. I have a load of numbers and some have a * followed by two digits. E.g.

12345
12322
1234*05
1223*01

What I'm trying to do is split these into two columns using a formula so that one column (left hand) has the numbers up to the * and the other (right hand) has everything after the *. In cases where there is no * the number should be reported to the left hand column and the right hand column should be blank.

I've been using the following formula:

=IF(ISNUMBER(SEARCH("*",K2)),MID(K2,1,FIND("*",K2)-1),K2)

Where column K is the one with the uncleaned number. This works fine for cases where there is an * in K but where there isn't I get a #VALUE returned. Does anyone have any idea how I can work around this so I get the original value back if there is no *?

Any help would be much appreciated - thanks in advance.

Regards

HA

[ChemistB]

Try this instead

=IF(ISNUMBER(SEARCH("*",K2&"*")),MID(K2,1,FIND("*",K2&"*")-1),K2)
or even
=MID(K2,1,FIND("*",K2&"*")-1)

[martindwilson]

just use text to columns delimited with * as delimiter

[Azam Ali]

try it
=IF(ISERROR(IF(ISNUMBER(SEARCH("*",K2)),MID(K2,1,FIND("*",K2)-1),K2))=TRUE,K2,IF(ISNUMBER(SEARCH("*",K2)),MID(K2,1,FIND("*",K2)-1),K2))

[housinganalyst]

Hi Azam many thanks for the quick solution. Can I just ask two quick questions in reply:

1) Why does the way I was doing it get a #VALUE?
2) How can I apply the formula you produced to give me the digits after the *?

Once again thanks for your help, it's much appreciated.

HA

[martindwilson]

@ azam ali??? seems overworked that!
in b1
=IF(ISNUMBER(FIND("*",A1)),LEFT(A1,FIND("*",A1)-1),A1)
in c1
=IF(ISNUMBER(FIND("*",A1)),MID(A1,FIND("*",A1)+1,255),"")

[ChemistB]

=SEARCH("*",K2) will see the * as a wildcard and return 1. FIND("*",K2) works as you'd expect it to. Switch to FIND or you can put a tilda ~ in front of the * to indicate to Excel that you are actually using * as a character and not a wildcard.

=IF(ISNUMBER(SEARCH("~*",K2)),MID(K2,1,FIND("*",K2)-1),K2)

[housinganalyst]

Many thanks to all who replied