If then multi cell output

[Nuclear_Dude]

Heres my question: I have data that looks like this:
...
445QQR99
445WWY90
465UXY85
0

UNKNOWN
'random text string'
...

What I want to do is split the cell according to what is in it. I was planning on using nested IF statements, but I need to know if it is possible to do the following:
C1=IF(ISNUMBER(LEFT(A1,3)),[LEFT(A1,3) and D1=MID(A1,4,5)],IF...
In laymans terms, i want to check if the first 3 make a number (e.g. 445 or 465 in above example), if true then C1 is the first 3, and D1 is the rest of the string, but if false go on to a different if statement.

Is this possible? Thanks in advance!!!!
NukeDude
----------
Point of clarification on raw data:
data can be a string of totally random letters, or a description, or blank, or 0, or it will be in the following form where '#' represents a digit, and '?' represents a character from the 26 letter alphabet: "###???##'. This form is one thing that I am specifically needing to split.

 

[T. Valko]

Heres my question: I have data that looks like this:
...
445QQR99
445WWY90
465UXY85
0

UNKNOWN
'random text string'
...

What I want to do is split the cell according to what is in it. I was planning on using nested IF statements, but I need to know if it is possible to do the following:
C1=IF(ISNUMBER(LEFT(A1,3)),[LEFT(A1,3) and D1=MID(A1,4,5)],IF...
In laymans terms, i want to check if the first 3 make a number (e.g. 445 or 465 in above example), if true then C1 is the first 3, and D1 is the rest of the string, but if false go on to a different if statement.

Is this possible? Thanks in advance!!!!
NukeDude
----------
Point of clarification on raw data:
data can be a string of totally random letters, or a description, or blank, or 0, or it will be in the following form where '#' represents a digit, and '?' represents a character from the 26 letter alphabet: "###???##'. This form is one thing that I am specifically needing to split.

Will any of the strings have leading zeros?

001XB12P
000BIFFNO1

 

[CharlesChuckieCharles]

In C1

=IF(ISNUMERIC(left(A1,3)),left(A1,3),A1)

in D1


=IF(ISNUMERIC(c1)),MID(A1,4),"")

 

[Nuclear_Dude]

Valko: No, there will never be leading zeros. All values will be in the hundreds.

Charles: that would work, but i am trying to find out if multi-cell assignment is possible, because in the case that the original data is a single '0', I want C1 to be 'UNKNOWN', and D1 to be the same as B1. Thats why I asked the question the way I did, I am trying to find out a general form, and then I can apply it to the multiple situations I need. Hope that helps.
-NukeDude

 

[T. Valko]

In C1

=IF(ISNUMERIC(left(A1,3)),left(A1,3),A1)

in D1


=IF(ISNUMERIC(c1)),MID(A1,4),"")

ISNUMERIC is a VBA function.

You probably meant ISNUMBER.

The LEFT function returns a TEXT string even if it looks like a number:

A1 = 123XYZ

=ISNUMBER(LEFT(A1,3)) will return FALSE

So, you need to convert the TEXT number to a numeric number:

=ISNUMBER(-LEFT(A1,3))

 

[T. Valko]

Valko: No, there will never be leading zeros. All values will be in the hundreds.
-NukeDude

Ok, then you can use something like:

C1:

=IF(N(-LEFT(A1,3)),--LEFT(A1,3),IF.....

The formula in D1 depends on what the rest of the formula in C1 looks like.

 

[CharlesChuckieCharles]

I think to answer your actual question,

no you can't update two or more seperate cells from a single formula

 

[Nuclear_Dude]

Charles:

I think to answer your actual question,

no you can't update two or more seperate cells from a single formula

Thanks. You got the verbage correct for my question. And if it is true that you cannot update two cells in a single formula then that answers my question. Can anyone else verify this?